Shopify Question posted in
The Shopify help center can be found here.

How to store time with timezone infomation in mysql datetime column? – Shopify

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?

Tags:

3

Answers


  1. 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.

    Login or Signup to reply.
  2. 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";
    Login or Signup to reply.
  3. 0

    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));
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search