MySQL now() change timezone

Question

I'm using the following INSERT statement:

INSERT INTO messages SET `to` = '".$to."', `from` = '".$this->userid."', `title` = '".$title."', `message` = '".$message."', `created` = NOW()

However, it uses my server time (America/Montreal). I want time zone of Asia (Asia/Calcutta)

Is this possible with the same query?

score 13 · Answer 1 · answered Feb 26 '18 at 10:12

13

Better use the SQL format directly in your query:

..`created` = CONVERT_TZ(NOW(),'SYSTEM','Asia/Calcutta')..

answered Feb 26 '18 at 10:12

Vlado

3,517
2
26
24

1

thanks for using SYSTEM instead of the other hacky solutions – Ofek Ron May 24 '20 at 07:01
thanks. See https://stackoverflow.com/questions/9808160/mysql-time-zones for more details on timezone – m50 Oct 20 '21 at 14:00

score 8 · Accepted Answer · answered May 23 '12 at 13:27

8

You would want to go ahead and use the CONVERT_TZ() function in MySQL. It's based off the Olson database which your operating system uses.

Here is the documentation.

answered May 23 '12 at 13:27

wesside

5,622
5
30
35

HI THX BUT NOT WORKING "INSERT INTO messages SET `to` = '".$to."', `from` = '".$this->userid."', `title` = '".$title."', `message` = '".$message."', `created` = CONVERT_TZ(NOW(), 'America/Phoenix', 'Asia/Calcutta)" – Harinder May 23 '12 at 13:57
Do a subselect on the created, SELECT CONVERT_TZ(now(),'US/Eastern','US/Central'); – wesside May 23 '12 at 14:00
Rather, you might need to populate the timezone tables, ref: http://stackoverflow.com/questions/2523286/mysql-convert-tz mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql – wesside May 23 '12 at 14:02

score 8 · Answer 3 · answered May 23 '12 at 13:33

After you open the connection to MySQL, run the following as a query:

SET time_zone = timezone;

Then all functions you do will run for that timezone for that connection (i.e. until you close the "link" to the database".

If you have the appropriate permissions you can lock it "permanently" / globaly. Tiemzone strings are standard strings as you have in your question.

http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

score 0 · Answer 4 · answered May 23 '12 at 13:33

$myDateTime = new DateTime('2012-05-23 17:01', new DateTimeZone('GMT'));
$myDateTime->setTimezone(new DateTimeZone('Asia/Kolkata'));
echo $myDateTime->format('Y-m-d H:i');

After modification to above code, such as desired format; you could use $myDateTime variable to insert into database.

MySQL now() change timezone

4 Answers4

Linked