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

Question

When embedding web applications on top of a MySQL database, I think it's nice to just use a string data type to store dates.

For example, I can just store dates as '201110191503999' in the database. It is also convenient for querying by date. For example, select * from some_table, where the_date, for example, '20111019%'

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

Thanks in advance!

Answer 1

Always use the column type for what you need; if you use date, use DATETIME ; if it's a timestamp, use TIMESTAMP , etc.

Depending on what you encode, all data formatting can be done on the actual page in any language that you use.

Alternatively, you can use MySQL functions such as NOW() instead of using language versions and then store them in a database.

Answer 2

If your data is of a date type, save the data in DATE (or DATETIME if you have a time element).

If you store dates as strings, then you are asking for problems! For example, what should prevent someone from writing the value "I'm not a date" in the field of the string "date"? Or what happens if you have “20111019” and “2011-10-19” and want them to be treated as equals? In addition, you will lose the entire MySQL DATE raft and certain time functions.

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

Answer 3

I am thinking of doing the same. I struggled with MySQL trying to get it to store a time-independent value in the database - something based on GMT. It really doesn't work. Tried all kinds of flags useTimeZone=true and JDBCShift with Java - still did not get any breakaway. Also, the Kuala Lampur time zone does not work due to some exotic Java message. So, if you can control the format, of course, use the String type. Many have done this before.