0

How to convert a timestamp into custom formated string with timezone in MySQL?

This is how I am selecting the timestamp right now. Its correctly formated, but lacks timezone. updated is the name of the timestamp field.

SELECT DATE_FORMAT(updated, '%e.%c.%Y %T') AS updated FROM table;

returns strings like this:

29.1.2015 12:43:16

Then I tried adding timezone like this, but I am getting NULLs as return values.

SELECT DATE_FORMAT(CONVERT_TZ(updated, 'GMT', 'Europe/Helsinki'), '%e.%c.%Y %T') AS updated FROM table;
Firze
  • 3,939
  • 6
  • 48
  • 61
  • About [CONVERT_TZ](http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_convert-tz): `This function returns NULL if the arguments are invalid.`. [Check your timezones.](http://dev.mysql.com/doc/refman/5.6/en/time-zone-support.html). Are they loaded? – Marcus Adams Jan 29 '15 at 13:16
  • Yes it seems neither of those timezones are valid. Everything works fine if I replace them with '-0:00' – Firze Jan 29 '15 at 13:22

1 Answers1

0

i use 1383123123 instead timestamp field

Select  FROM_UNIXTIME(1383123123);

the result is '2013-10-30 10:52:03'

Select  CONVERT_TZ(FROM_UNIXTIME(1383123123), '+00:00', '+02:00')

the result is '2013-10-30 12:52:03'

'Europe/Helsinki' time zone means "gmt+2"

sddk
  • 1,115
  • 1
  • 10
  • 20
  • 1
    I know that numeric values can be used, but it won't take daylight savings time in consideration. I thought there is a function that would automatically handle that, but I guess there isn't. – Firze Jan 29 '15 at 13:32
  • an interesting topic you may had look at it http://stackoverflow.com/questions/1646171/mysql-datetime-fields-and-daylight-savings-time-how-do-i-reference-the-extra – sddk Jan 29 '15 at 13:55