Storing Datetime as Utc in PHP/Mysql

Storing datetime as UTC in PHP/MySQL

MySQL: UTC_TIMESTAMP()

Returns the current UTC date and time
as a value in 'YYYY-MM-DD HH:MM:SS' or
YYYYMMDDHHMMSS.uuuuuu format,
depending on whether the function is
used in a string or numeric context

PHP: gmdate()

Also PHP date_default_timezone_set() is used in PHP to set the current time zone for the script. You can set it to the client time zone so all the formatting functions return the time in his local time.

In truth though I had a hard time getting this to work and always stumble into some gotcha. Eg. time information returned from MySQL is not formatted as 'UTC' so strtotime transforms it into a local time if you are not careful. I'm curious to hear if someone has a reliable solution for this problem, one that doesn't break when dates traverse media boundaries (HTTP->PHP->MySQL and MySQL->PHP->HTTP), also considering XML and RSS/Atom.

PHP + MySQL storing UTC timestamp vs datetime

Your workflow is fine.

Whenever you need to fulfil multi timezone requirement, it is better to store time in database as utc timestamp or unix epoch. unix epoch is well supported and i would suggest this. Even from the client side you can send unix epoch to the server without worrying about timezone. If you use utc timestamp you will need to convert between timezones. with unix epoch it is still conversion but it is very straight forward and well supported in most of the date object constructs.

And when you need to retrieve and display time, you will construct the date object using unix epoch and the time zone for that user.

How to store time UTC in MySQL?

It is up to you to decide the best way to solve timezone problem when users and server has different locale.

No matter the case and the app (mobile, web, etc.) the problem is the same. You should find the best and easiest in your case way to handle time zones.

Here are few options that you can use:

MySQL

From MySQL Date and Time Types - you can create table fields that will hold your date and time values.

"The date and time types for representing temporal values are DATE, TIME, DATETIME, TIMESTAMP, and YEAR. Each temporal type has a range of valid values, as well as a “zero” value that may be used when you specify an invalid value that MySQL cannot represent. The TIMESTAMP type has special automatic updating behavior, described later."

In respect to MySQL Data Type Storage Requirements read the link and make sure you satisfy the table storage engine and type requirements in your project.

Setting the timezone in MySQL by:

SET time_zone = '+8:00'

To me this is a bit more work to handle, but the data is fully loaded, managed and updated by MySQL. No PHP here!

Using MySQL might seem like a better idea (that's what I'd like to think), but there's a lot more to it.

To be able to choose, you will have to make an educated decision. There's a lot to cover in regards to using MySQL. Here's a practical article that goes into the rabbit hole of using MySQL to manage date, time and timezone.

Since you didn't specify how you interface the database, here's a PHP example and functions to handle the date, time and time zones.

PHP

1. Save date, time and time zone

E.g. Chicago (USA - Illinois) - UTC Offset UTC -5 hours
You can save the date time

2015-11-01 00:00:00

and the time zone

America/Chicago

You will have to work out DST transitions and months having different numbers of days.

Here's a reference to the DateTime to work out any timezone and DST differences:

DateTime Aritmetic

2. Unix Timestamp and Time Zone

Before we go into the details of this option we should be aware of the following:

The unix time stamp is a way to track time as a running total of seconds. This count starts at the Unix Epoch on January 1st, 1970 at UTC. Therefore, the unix time stamp is merely the number of seconds between a particular date and the Unix Epoch. It should also be pointed out (thanks to the comments from visitors to this site) that this point in time technically does not change no matter where you are located on the globe. This is very useful to computer systems for tracking and sorting dated information in dynamic and distributed applications both online and client side.

What happens on January 19, 2038?

On this date the Unix Time Stamp will cease to work due to a 32-bit overflow. Before this moment millions of applications will need to either adopt a new convention for time stamps or be migrated to 64-bit systems which will buy the time stamp a "bit" more time.

Here's how the timestamp works:

08/19/2019 @ 8:59pm (UTC) translates to 1566248380 seconds since Jan 01 1970. (UTC)

Using the PHP date() function you can format to anything you want like:

echo date('l jS \of F Y h:i:s A', 1566248380);

Monday 19th of August 2019 08:59:40 PM

or MySQL:

SELECT from_unixtime(2147483647);

+--------------------------------------+

| from_unixtime(2147483647) |

+--------------------------------------+

| 2038-01-19 03:14:07 |

+--------------------------------------+

More example formats that you can convert to:

08/19/2019 @ 8:59pm (UTC)

2019-08-19T20:59:40+00:00 in ISO 8601

Mon, 19 Aug 2019 20:59:40 +0000 in RFC 822, 1036, 1123, 2822

Monday, 19-Aug-19 20:59:40 UTC in RFC 2822

2019-08-19T20:59:40+00:00 in RFC 3339

The PHP Date() function can be used as a reference.

Again you will have to save the time zone:

America/Chicago

Set the PHP script time zone for your users by using date_default_timezone_set() function:

// set the default timezone to use. Available since PHP 5.1

date_default_timezone_set('UTC');

date_default_timezone_set('America/Chicago');

How to save time as UTC time in php/mysql?

gmdate('U'); outputs the GMT/UTC timestamp of the specified timestamp based on the current timezone.

See more information on the PHP Manual page

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

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));

Correctly converting to timezone from UTC in MySQL database with PHP

It is not incorrect. Your date is stored without the time component, which means when you put it in a DateTime constructor, it will be created as midnight ($dateNeeded = new DateTime($row['dateNeeded']) will have the value of 2020-03-03 00:00:00.

That's in UTC timezone because you defined it so at the start. So when you change the timezone, it will move back (as New York is UTC - 5) and gain the value of 2020-03-02 19:00:00.

Since you're only outputting the date in your format (and not the time), it comes out as a day early.

How to store a datetime in MySQL with timezone info

You said:

I want them to always come out as Tanzanian time and not in the local times that various collaborator are in.

If this is the case, then you should not use UTC. All you need to do is to use a DATETIME type in MySQL instead of a TIMESTAMP type.

From the MySQL documentation:

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)

If you are already using a DATETIME type, then you must be not setting it by the local time to begin with. You'll need to focus less on the database, and more on your application code - which you didn't show here. The problem, and the solution, will vary drastically depending on language, so be sure to tag the question with the appropriate language of your application code.

---

Related Topics