0

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?

kenpeter
  • 7,404
  • 14
  • 64
  • 95

3 Answers3

2

What exactly 2018-12-16T17:36:29+11:00 means?

This is a UTC offset timestamp, so you have:

UTC Date: `2018-12-16`
UTC Time: `17:36:29`
Offset: `+11:00`

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:

|    Date                  |   Timezone  |
==========================================
|    2018-12-16 17:36:29   |   +11       |

This way, you can now get an accurate time in the desired timezone. Example:

$strtime = $record['Date'].' '.$record['Timezone']' hours';
echo date('Y-m-d H:i:s', strtotime($strtime));
Jonathan
  • 510
  • 3
  • 12
  • 2
    Note that a `timezone` column (which is actually more accurately `time_offset`) should not be an integer because offsets can be half-hour or even quarter-hour in certain timezones. – Matt Mc Nov 23 '19 at 00:12
0

I'm not familiar with shopify, but it looks like a UTC offset, so it would be:

date: 2018-12-16,
time: 06:36:29,
timezone: UTC+11:00

As for your second question, you don't:

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.

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 saving 2018-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.

Varstahl
  • 575
  • 4
  • 14
  • Please don't recommend the `TIMESTAMP` column. Use `DATETIME`. Most of your answer is correct otherwise. – Matt Mc Nov 23 '19 at 00:11
0

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:

$time = '2018-12-16T17:36:29+11:00';
$created = date('Y-m-d H:i:s', strtotime($time)); //result: 2018-12-16 09:36:29
$update = "UPDATE db.table SET shopify_created_at='".$created."' WHERE id=X";
BillyK.
  • 71
  • 1
  • 6
  • That would store the timestamp relative to when the query was ran, it won't save the timestamp required by the OP. Since they can run asynchronously, it could drift quite a bit. – Varstahl Dec 19 '18 at 01:17
  • Thanks for your comment. I misunderstood the question. I have updated my answer and should get the right result now. – BillyK. Dec 19 '18 at 01:40