MySQL DATE_FORMAT: Difference to Greenwich time format

Question

I am trying to find the equivalent of PHP's "Difference to Greenwich time (GMT) with colon between hours and minutes Example: +02:00" date format for MySQL's DATE_FORMAT function.

PHP Example:

<?php
   echo date('P');
   //+02:00

I have a date field I am trying to format to ISO 8601. I have most of the format but I am missing the last part which is the "Difference to Greenwich time" AKA "+00:00".

MySQL Example:

SELECT DATE_FORMAT(date, "%Y-%m-%dT%T) as date FROM table;
//returns  2016-03-03T16:03:01
//required 2016-03-03T16:03:01+00:00

Documention Links:

http://php.net/manual/en/function.date.php

http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format

Might be worth checking this SO question, see if it helps : http://stackoverflow.com/questions/805538/in-mysql-caculating-offset-for-a-time-zone - replace `NOW()` with the given date and it *should* work. — CD001, Mar 03 '16 at 16:11
@CD001 my date field will contain past and future dates, will comparing it to UTC_TIMESTAMP() give me a correct answer? — Theo Kouzelis, Mar 03 '16 at 16:42
Ummm... good point; you're likely to end up with gibberish like `GMT -123449232:00` ... Personally I tend to leave dates in the database as UTC and let the application juggle it based on timezone; but I'm not sure what your ultimate objective is here. — CD001, Mar 03 '16 at 16:51
I was making a report from a MySQL query and I wanted the dates to stay in the same format as my PHP program outputs them using $datetime->format('c'); and it seemed more convenient to format the data in MySQL in this instance. But its not a big issue there are other ways around it. — Theo Kouzelis, Mar 03 '16 at 17:06

score 0 · Answer 1 · answered Mar 03 '16 at 15:58

I use this to set php and mysql to the same timezone

    $tz = 'UTC';
    date_default_timezone_set ( $tz);
    $now = new DateTime();
    $mins = $now->getOffset() / 60;
    $sgn = ($mins < 0 ? -1 : 1);
    $mins = abs($mins);
    $hrs = floor($mins / 60);
    $mins -= $hrs * 60;
    $offset = sprintf('%+d:%02d', $hrs*$sgn, $mins);
    define('DB_TIMEZONE', $offset);
    define('PHP_TIMEZONE', $tz);

score 0 · Answer 2 · answered Mar 03 '16 at 15:59

You could do something like this:

select timediff( now(), convert_tz(now(), 'America/Los_Angeles', 'Asia/Singapore'));

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz

https://dev.mysql.com/doc/refman/5.5/en/date-and-timefunctions.html#function_timediff

http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_now

MySQL DATE_FORMAT: Difference to Greenwich time format

2 Answers2