-1

In my Mysql table date time is saving in TIMESTAMP (UTC) format, I want to convert it to another timezone. But I am getting NULL for following query 

SELECT CONVERT_TZ('1575952663','UTC','Pacific/Kiritimati');

I tried another method 

SELECT CONVERT_TZ(FROM_UNIXTIME('1575952663'),'UTC','Pacific/Kiritimati');

but it is wrong time is getting because FROM_UNIXTIME converting from Asia/Kolkata because my mysql server timezone is Asia/Kolkata. This query is running from php side

Elby
  • 1,624
  • 3
  • 23
  • 42
  • Does this answer your question? [Can MySQL convert a stored UTC time to local timezone?](https://stackoverflow.com/questions/2187593/can-mysql-convert-a-stored-utc-time-to-local-timezone) – Ingus Dec 10 '19 at 08:54
  • Does this answer your question? [convert\_tz returns null](https://stackoverflow.com/questions/14454304/convert-tz-returns-null) – Presmelito Dec 10 '19 at 08:58
  • When i run the second command on sqlfiddle it returns `2019-12-10T18:37:43Z`. Is that the expected output? If so, the problem must be server side? – jared Dec 10 '19 at 08:59
  • `SELECT CONVERT_TZ(FROM_UNIXTIME('1575952663'), @@SESSION.time_zone, 'Pacific/Kiritimati');` I would give this a try. – Roland Starke Dec 10 '19 at 09:02
  • I feel like @Elby has not even searched for possible issues. .. – Ingus Dec 10 '19 at 09:02

2 Answers2

0

This is invalid parameter for convert_tz based on mysql time zone support.

For 'Pacific/Kiritimati', it should '+14:00'.

convert_tz(from_unixtime(1575952663),'UTC','+14:00')
Ed Bangga
  • 12,879
  • 4
  • 16
  • 30
0

You can convert it like below.

CONVERT_TZ(date,'+00:00','-07:00');