CODEWITHSUNDEEP
phpmysqlsql
smart-factorydotnet
smart-factorydotnet

Reputation: 73

How to convert time zone within mysql statement

I am trying to convert a unix timestamp into a local time using mysql.

The statement I am trying is:

SELECT CONVERT_TZ('FROM_UNIXTIME(table1.timestamp)', 'GMT', 'MET' ),table1.COL2,table1.COL3 WHERE table1ID ='3'

It returns NULL.

I would prefer to do this directly in mysql rather than manipulating the output later using php to keep other parts of my code simple.

Thanks in advance for any help.

Upvotes: 1

Views: 74

Answers (2)

Denis
Denis

Reputation: 5271

Quotes around FROM_UNIXTIME(table1.timestamp) make that a string, which does not look like a date. You have to remove the quotes to make it a function call.

Once that is done, you still might get NULL. Test it out by running a test query: SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');

If you still get NULL, you probably need to set up your time zone tables. See MySQL documentation on topic.

Upvotes: 0

Mark
Mark

Reputation: 8441

Try to remove the quotation on your 'FROM_UNIXTIME(table1.timestamp)' like:

SELECT CONVERT_TZ(FROM_UNIXTIME(table1.timestamp), 'GMT', 'MET' ),table1.COL2,table1.COL3 WHERE table1ID ='3'

Upvotes: 2

Related Questions