How do I store an UTC ISO8601 date in a MySQL database?
Solution 1
You can use DateTime
data type for storing the date and time.
Use CAST
function to cast such strings into mysql DateTime
type.
Here is an example:
CAST("2011-10-02T23:25:42Z" AS DATETIME)
This will give you 2011-10-02 23:25:42
.
Hope this will help you.
Solution 2
You can not store date in raw UTC ISO8601 format (with 2011-10-02T23:25:42Z
representation) and save all SQL DATETIME functionality.
But you should know, that MySQL ( regarding to http://dev.mysql.com/doc/refman/5.5/en/datetime.html ) always store time/date in UTC. Also you can modify timezone for your connection http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html
So, if you execute in PHP
date_default_timezone_set('UTC');
and in MySQL
SET time_zone = +00:00
sure PHP and MySQL would use UTC.
After that you can convert all database strings to DateTime without caring about timezone mismatch.
To convert any PHP DateTime (without carrying about its internal timezone) to MySQL datetime string you should set DateTime object timezone to UTC.
$datetime->setTimezone(new DateTimeZone('UTC'))->format('Y-m-d H:i:s');
Solution 3
You can easily convert the date using strtotime
function of php
:
date_default_timezone_set('UTC');
$date = '2011-10-02T23:25:42Z';//(aka ISO 8601 in UTC)
$time = strtotime($date); //time is now equals to the timestamp
$converted = date('l, F jS Y \a\t g:ia', $time); //convert to date if you prefer, credit to Marc B for the parameters
Now you would simply insert your date in MySQL
using timestamp
or datetime
depending on which one fit the most your needs. Here the most important things you should know about both types.
Timestamp
- Range of '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC
- Affected by the time-zone setting.
- 4 bytes storage
- allow
on update current_timestamp
on columns for all versions. - Index is way faster
-
NULL
is not a possible default value - Values are converted from the current time zone to
UTC
for storage, and converted back fromUTC
to the current time-zone for retrieval.
Datetime
- Range of '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
- Constant (time-zone won't affect)
- 8 bytes storage
- allow update on columns only as of version
5.6.5
Which is best for comparison (eg. getting records between two dates/times) and ordering the results from queries? What about if the database is very large?
According to the previous points I stated, then you should use timestamp
for a very large database as the storage is smaller, and the index faster which will give you better performance for comparison. However, you MUST MAKE SURE your date will fit the limits of the timestamp
I previously mentioned, else you have no choice and must use datetime
.
Documentation for strtotime
: http://php.net/manual/en/function.strtotime.php
And please, for the sake of SO's answerer who keep repeating every day to not use the mysql*
DEPRECATED functions, please use PDO
or mysqli*
when you will do your inserts.
http://php.net/manual/en/book.pdo.php
http://php.net/manual/en/book.mysqli.php
Solution 4
Using your datetime on my system which is PDT:
SELECT CONVERT_TZ(str_to_date('2011-10-02T23:25:42Z','%Y-%m-%dT%H:%i:%sZ'),'+00:00','SYSTEM') from dual;
2011-10-02 16:25:42
If your datetime has a fractional microsecond; include the .%f before the Z as follows:
SELECT CONVERT_TZ(str_to_date('2011-10-02T23:25:42.123456Z','%Y-%m-%dT%H:%i:%s.%fZ'),'+00:00','SYSTEM') from dual;
2011-10-02 16:25:42.123456
user967144
Updated on September 22, 2020Comments
-
user967144 over 3 years
I have thousands of dates in the following format:
2011-10-02T23:25:42Z
(aka ISO 8601 in UTC)What MySQL data type should I use for storing such a ISO8601 date in a MySQL database? E.g.
Datetime
,timestamp
or something else?Which is best for comparison (eg. getting records between two dates/times) and ordering the results from queries? What about if the database is very large?
And what would be the best way to convert the above PHP string for MySQL storage? (I'm guessing
date_default_timezone_set('UTC');
would be used?)