CODEWITHSUNDEEP
mysqlstringdatetimedate
Jake W
Jake W

Reputation: 2858

Is it a good idea to use string data type for dates in MySQL instead of using datetime data type?

While implementing web applications on top of MySQL database, I'm thinking if it is a good idea to just use string data type to store dates?

For example, I can just store dates as '201110191503999' into database. Also this is convenient to query by date. For example, select * from some_table where the_date like '20111019%'

Is there any performance issue if we use string for dates? and are there any advantages for using date/datetime data type?

Thanks in advance!

Upvotes: 6

Views: 2956

Answers (3)

nvcode
nvcode

Reputation: 343

Always use the column type for what what is needed; if you are using a date, use DATETIME, if it is a timestamp, use TIMESTAMP and so on.

Depending on in what you are coding, all the formatting of the data can be done on the actual page in whatever language you are using.

Also, you can take advantage of MySQL functions such as NOW(), rather than using the language's version and then storing it into the database.

Upvotes: 7

Jim
Jim

Reputation: 11

I am thinking of doing the same thing. I have been wrestling with MySQL trying to get it to store a timezone independent value in the database - something based off GMT. It is really not working. Tried all kinds of flags useTimeZone=true and JDBCShift with Java - still not getting any liftoff. Also Kuala Lampur timezone does not work because of some exotic Java message. So, if you can control the format, sure, use a String type. Many have done it before.

Upvotes: 1

Tom Mac
Tom Mac

Reputation: 9853

If your data is of date type then store the data in a DATE (or DATETIME if you have a time element to it).

If you store dates as strings then you are asking for trouble! For example, what is to stop somebody writing a value of 'I am not a date' into you string 'date' field? Or what happens if you have '20111019' and '2011-10-19' and want them to be treated as equal? Furthermore you will be missing out on a whole raft of MySQL DATE and TIME specific functions

Never store a date as a string if you can possibly avoid it.

Upvotes: 7

Related Questions