How do I store an UTC ISO8601 date in a MySQL database?

Question

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

Wilbertwilborn

Have look at this question: https://mcmap.net/q/40410/-should-i-use-the-datetime-or-timestamp-data-type-in-mysql – Gamba

this helps for my case: STR_TO_DATE('2016-10-19T00:57:38+0000','%Y-%m-%dT%H:%i:%s+%x') +%x is because the timezone digits in php's DateTime::ISO8601 eg. +0000 seems to have no specified representation in mysql, so using %x for unknown, even STR_TO_DATE('2016-10-19T00:57:38+0000','%Y-%m-%dT%T') but it raises warning for truncated datetime ps: for '2016-10-19T00:57:38+0000', use '%Y-%m-%dT%H:%i:%s+%x' as format specifier in mysql – Muniz

Answer 1

I think that keeping your date-time values in field of type DATETIME would be kind of natural way.

From my own experience with my current PHP application, only read / write operations concerning this information may be problematic.

One of possible solutions (assuming that you use DATETIME data type) for properly performing the whole process could be the following approach:

Reading DATETIME values for PHP use

1. Acquire DATETIME fields from your database converting them in the query to string representation in the form of '2011-10-02T23:25:42Z' by using DATE_FORMAT MySQL function with '%Y-%m-%dT%H:%i:%sZ' formatting string (docs on DATE_FORMAT)
2. Read fetched column value in this specific format and convert it in PHP from string to real date-time representation valid for PHP (such as DateTime class objects and DateTime::createFromFormat static method given 'Y-m-d\TH:i:s\Z' formatting string (T and Z are escaped to avoid treating them as formatting directives) (docs for the method).
3. Use converted values as real date-time values with all the logic applicable, like real date comparisons (not text-comparisons), etc.

Writing PHP date-time to MySQL database

1. Convert i.e. PHP DateTime class object to our ISO 8601 in UTC format string representation using DateTime class object's format method with the same as before 'Y-m-d\TH:i:s\Z' formatting string (documentation).
2. Perform INSERT / UPDATE operation on database information using such prepared string as a parameter for MySQL function STR_TO_DATE (with '%Y-%m-%dT%H:%i:%sZ' formatting string) which converts it to real database DATETIME value (docs on STR_TO_DATE).

Example code in PHP

Below please find a draft example of such approach using PDO objects:

$db = new PDO('mysql:host=localhost;dbname=my_db;charset=utf8', 'username', 'password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {
    // run the query aquring 1 example row with DATETIME data 
    // converted with MySQL DATE_FORMAT function to its string representation 
    // in the chosen format (in our case: ISO 8601 / UTC)
    $stmt = $db->query("SELECT DATE_FORMAT(dt_column, '%Y-%m-%dT%H:%i:%sZ') AS formatted_dt_col"
                        ." FROM your_table LIMIT 1"); 

    if($stmt !== FALSE) {
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        // convert the acquired string representation from DB 
        // (i.e. '2011-10-02T23:25:42Z' )
        // to PHP DateTime object which has all the logic of date-time manipulation:    
        $dateTimeObject = DateTime::createFromFormat('Y-m-d\TH:i:s\Z', $row['formatted_dt_col']);

        // the following should print i.e. 2011-10-02T23:25:42Z
        echo $dateTimeObject->format('Y-m-d\TH:i:s\Z');  

        // now let's write PHP DateTime class object '$dateTimeObject' 
        // back to the database
        $stmtInsertDT = $db->prepare("INSERT INTO your_table(dt_column) " 
                             . " VALUES ( STR_TO_DATE(:par_formatted_dt_column, '%Y-%m-%dT%H:%i:%sZ') )");

        $dtAsTextForInsert = $dateTimeObject->format('Y-m-d\TH:i:s\Z');

        // convert '$dateTimeObject' to its ISO 8601 / UTC text represantation
        // in order to be able to put in in the query using PDO text parameter
        $stmtInsertDT->bindParam(':par_formatted_dt_column', $dtAsTextForInsert, PDO::PARAM_STR);

        $stmtInsertDT->execute();

        // So the real insert query being perform would be i.e.:
        /*
           INSERT INTO your_table(dt_column) 
           VALUES ( STR_TO_DATE('2011-10-02T23:25:42Z', '%Y-%m-%dT%H:%i:%sZ') )
        */
    }
}
catch(\PDOException $pexc) {
 // serve PDOException
}
catch(\Exception $exc) {
// in case of no-PDOException, serve general exception
}

This approach helped me a lot in operating date-time values between PHP and MySQL database.

I hope it might occur helpful for you also.

Hialeah

Ashish's solution of using CAST seems to be the better/easier way to convert. – Rambunctious

Ok. No offence. This is completely your choice. Regards. – Hialeah

I was just wondering if there was a reason you didn't go for it. – Rambunctious

Using '%Y-%m-%dT%H:%i:%sZ' with DATE_FORMAT is fatal if you don't have the guarantee that the time zone is '+00:00'. DATETIME stores the time value as given during insert. You need to be sure that all INSERTs are using the same time zone. – Achromatize

Answer 2

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.

Tieck

Inserting CAST('2020-01-08T06:28:43Z' AS DATETIME), gives me an error #1292 - Truncated incorrect datetime value: '2020-01-08T06:28:43Z' – Carrollcarronade

as same as CAST('2020-05-19T19:03:56+02:00' AS DATETIME)… not working, I must remove +02:00 first, then it will be working – Tennies

Answer 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 from UTC 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

Chimera

Answer 4

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

Voluminous

Answer 5

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

Crucifer

Note that .%f only works up to six decimal places. If you have seven (.NET DateTimeOffset), you need to truncate it or you'll get an error about invalid date/time. – Arcturus

Answer 6

Here are the points why it is better to use datetime.

1. With datetime you will be able to do date manipulations on mysql side - such as subtracting day,month
2. You will be able to sort data.
3. If DB is huge - varchar takes more place on HDD

Santalaceous

When i inserted '2011-10-02T23:25:42Z' into DB it gives me a Warning: #1264 Out of range value for column 'tz' at row 1 – Wilbertwilborn

@Wilbertwilborn column tz should be of type 'datetime' and the value you pass for insertion you should convert using php date('Y-m-d H:i:s',strtotime('2011-10-02T23:25:42Z')).. hope this helps – Plier

@Wilbertwilborn beware though that the letter 'Z' at the end of your date will make PHP convert the time to the server's timezone..if you want to store the GMT time you will have to remove the 'Z' from the end of the date... – Plier

You can still insert the string as long as you use MySQL's STR_TO_DATE('2011-10-02T23:25:42Z', '%Y-%m-%dT%TZ') to let MySQL convert it to a datetime. (see dev.mysql.com/doc/refman/5.5/en/…) – Correggio

@RolandBouman thanks for the tip. The above format string return null on an ISO date with fraction seconds. However this format - select str_to_date('2014-03-21T22:27:22.392423Z' , '%Y-%m-%dT%H:%i:%s' ) from dual; works. – Grey