CODEWITHSUNDEEP
phpmysqltimezone
Harinder
Harinder

Reputation: 1257

MySQL now() change timezone

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?

Upvotes: 12

Views: 56752

Answers (6)

Debro Roy
Debro Roy

Reputation: 11

  1. To set a Session Timezone (GMT +5.30)
SET time_zone = "+05:30";
  1. Query (considering "processedAt" as datetime)
SELECT * FROM table WHERE processedAt < NOW() AND processedAt IS NOT NULL;
  1. Check the Global and Current Session timezone
SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;

Or if you are using PHP, set this at the top of your script

date_default_timezone_set("Asia/Kolkata");

and use date('Y-m-d H:i:s') instead of Mysql NOW()

Upvotes: 0

라도훈
라도훈

Reputation: 1

Small change from @Vlado 's answer

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

Cus now() method will use timezone variable even when global timezone changed or session timezone changed

Upvotes: 0

Vlado
Vlado

Reputation: 3787

Better use the SQL format directly in your query:

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

Upvotes: 16

Robbie
Robbie

Reputation: 17710

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

Upvotes: 8

Vishal
Vishal

Reputation: 2181

$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.

Upvotes: 0

wesside
wesside

Reputation: 5740

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.

Upvotes: 8

Related Questions