Which type I should use to store current date + time in UTC?
Then to be able to convert UTC date to specific timezone?
Now I use TIMESTAMP type and CURRENT_TIMESTAMP.
It stores data like: 2019-08-19 20:44:11
But minutes are different that real UTC time, I dont know why.
My server time is local. It is correct under Windows Server
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:
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');
You can't store a date/time with time zone information.
MySQL does not store the time zone information on either DATETIME or TIMESTAMP. They are assumed to be on the server time zone.
The only ugly work around is to set the whole MySQL server/vm/docker container to UTC.
