Another dateTime question

Question

I currently have a date in this format

2010-03-03 10:39:18

which is a TIMESTAMP field in MySQL. I need to have the date in this format for a search engine called Solr:

1995-12-31T23:59:59Z

Here is some text from their website about dates:

Solr expects dates to be in UTC when indexing. The format for this date field is of the form 1995-12-31T23:59:59Z, and is a more restricted form of the canonical representation of dateTime http://www.w3.org/TR/xmlschema-2/#dateTime. The trailing "Z" designates UTC time and is mandatory. Optional fractional seconds are allowed: 1995-12-31T23:59:59.999Z All other components are mandatory.

I was given this code from another Q here on SO, but it doesn't work. Solr complains about an "invalid time string":

$solr_date = date('c', (strtotime($date_from_mysql)); // doesn't work

When echoing $solr_date, the trailing Z mentioned above isn't there. Thanks.

Answer 1

Here it is PHP solution :

Split your ( 2010-03-03 10:39:18 ) date string based on the mktime function arguments.

$solr_date = date('c', mktime($year,$month,$date, $h, $m , $s)); # it will work

Answer 2

First of all, it's worth noting that date/time columns in MySQL are not stored in any specific string format. They are converted into strings when printing them and you can choose how to format them.

If you want a pure MySQL solution, the functions that are relevant to your problem include CONVERT_TZ() to obtain the UTC time and DATE_FORMAT() to display it as required. E.g.:

SELECT CURRENT_TIMESTAMP,
    CONVERT_TZ(CURRENT_TIMESTAMP, '+01:00', '+00:00'),
    DATE_FORMAT(CONVERT_TZ(CURRENT_TIMESTAMP, '+01:00', '+00:00'), '%Y-%m-%dT%H:%i:%sZ')

The problem is basically that you have to know your timezone beforehand since TIMESTAMP columns do not allow to store such information. Also, you may find issues with daylight saving time.

About PHP, the date() function allows to combine format code, you're not limited to only one:

<?php
$ts = strtotime($date_from_mysql);
$solr_date date('Y-m-d', $ts) . 'T' . date('H:i:s', $ts) . 'Z';
?>

Answer 3

Why don't you just convert it to UTC?

    $datetime = "2010-01-19 00:00:00";
    echo "Datetime Before: ".$datetime."<br />";
    $dttostr = strtotime("2010-01-19 00:00:00");
    echo "Datetime After: ".formatToUTC($dttostr)."<br />";
    echo "System Timezone: ".date_default_timezone_get()."<br />";

    function formatToUTC($passeddt) {
        // Get the default timezone
        $default_tz = date_default_timezone_get();

        // Set timezone to UTC
        date_default_timezone_set("UTC");

        // convert datetime into UTC
        $utc_format = date("Y-m-d\TG:i:s\Z", $passeddt);

        // Might not need to set back to the default but did just in case
        date_default_timezone_set($default_tz);

        return $utc_format;
    }