Datetime format with time zones

Question

I am pulling date from API as a string in this format: yyyy-MM-dd'T'HH:mm:ss'Z'

The problem is when I try to save it, let's say today 2015-01-23T13:42:00Z the flags Z and T are not shown in MySQL database (the date is saved like this 2015-01-23 13:42:00).

I would like to keep the field date type, i.e. I don't want to save the date into varchar field.

I am not familiar with date timezone format, so any suggestions are welcome.

Answer 1

First, read about the DATETIME, and TIMESTAMP types in the mysql docs.

Then, make a decision on which field type to use:

• If you want to submit, store, and retrieve the same value without ever having MySQL perform time zone conversions, then use a DATETIME field.

• If you want to submit and retrieve values to/from the field using a specific time zone, but actually store a UTC time, then use TIMESTAMP.

  • Consider that you might want to still use a TIMESTAMP type for UTC-based values, but just set the session time zone explicitly to UTC.

• Never ever store date/time values in a VARCHAR field.

Finally, realize that there's a huge difference between what is actually stored in the database, versus what you see when you examine the data. The database is storing things efficiently, in a non-visible binary representation. You simply observe that value projected to a specific format during display. The format you use when you submit the data helps to craft the binary value, but then those strings are discarded. Later when you look at the field, if you print them to the screen then it converts back to a string so it can show you the value.

It's no different then if you sent an integer as "1,234" - it doesn't actually store that string, it stores 0x04D2 (or 0000 0100 1101 0010 in binary). You could read that back as "1234" or "1,234" and the formatting of putting the comma in the right place has nothing whatsoever to do with how the value was stored. It only has to do with how the tool that shows you the value renders it for output. The same thing is going on with your date formatting scenario.