Currently, I have this time stamp from shopify api (2018-12-16T17:36:29+11:00) that I need to store in a datetime field in mysql.
1st question:
What exactly 2018-12-16T17:36:29+11:00 means?
date: 2018-12-16,
time: 17:36:29,
timezone: +11:00?????
2nd question:
If I want to store the time stamp into a mysql datetime column.
I get this error
SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2018-12-16T17:36:29+11:00' for column 'shopify_created_at' at row 1
What is the correct way to store time with time zone info?
3
Answers
I’m not familiar with shopify, but it looks like a UTC offset, so it would be:
As for your second question, you don’t:
All timestamps are timezone free. You can either save the timezone separately, or save it as a string. The best way to go would be to convert the
2018-12-16T17:36:29+11:00
string by subtracting the timezone from the clock, and then saving2018-12-16T06:36:29
and+11:00
separately. If you don’t need the timezone reference for further use, you can just normalize the time and discard the TZ specification.Also, depending on your needs, you could probably do both things. Normalizing the timestamps will help with sorting in a reliable manner, TZ free, while you could store and retrieve the full string if you need it for other shopify queries.
timezone: +11:00 = UTC+11:00 is an identifier for an +11-hour time offset from UTC. This time is used in: North Asia, Australia and some other countries.
As for the timestamp you can simply use:
This is a UTC offset timestamp, so you have:
I’m not familiar with Shopify and their APIs, however, if it is important to know / store the timezone, I would store the UTC time in one column of your database (using
datetime
fieldtype), and the offset hours in another column.Example:
This way, you can now get an accurate time in the desired timezone. Example: