CODEWITHSUNDEEP
phpmysqldatetimetimestamp
iLaYa ツ
iLaYa ツ

Reputation: 4017

Calculate the difference between two timefields

Hi i am using third party API to display the feeds in our site, in that i have fields called opentime and closetime in db table. The feed table is being updated for every 20 seconds .Things working fine, now the problem is with opentime and closetime. Its giving 3 Hrs time in the future(it might be their server time) from the current time in my server. Say example, if my server current time is 8:00 AM, at the time feed table is being updated the opentime as 11:00 AM(3 Hrs from now, sometime its varying). We can't do any changes with feed table since the table is updating automatically through API call.

So i want to find out the time difference between these two(opentime - currenttime), how can i able to convert the opentime into my server time.

I tried the following code in my-sql query,

TIMESTAMPADD(MINUTE,-180,FROM_UNIXTIME(opendate) // subtracting 3 hours(180 min) from the opendate.

Is there any other way to do this?

Advance thanks for your guidance.

Upvotes: 0

Views: 157

Answers (2)

Alastor
Alastor

Reputation: 357

Set timezone for mysql config

set timezone='your timezone';

Try it

SELECT TIMESTAMPDIFF(MINUTE,'2012-10-21', NOW()) as diff;
diff=1997

Upvotes: 0

Joshua Kaiser
Joshua Kaiser

Reputation: 1479

You can change the default time zone for MySQL. This can be set as a default for your MySQL server, or on the fly with each connection. Something like this:

set timezone='your timezone';

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

Upvotes: 2

Related Questions