Help with mysql timezone conversion

[figment88]

The mysql time/date functions always seem to give me the most trouble.

I want to insert the current time into the database into a time type column basic format hh:mm:ss

The web server is located on the East Coast and I'm on the West Coast, so I want to subtract three hours. There seem to be about 80 ways to do this but I can't get the syntax right on any of them.

Appreciate any syntax snippet of the form
insert into contacts set cur_time= ...

[jatar_k]

<?
$pst = mktime() - 10800; // 3 hours in seconds 3*60*60
$pstime = strftime('%H:%M:%S',$pst);
echo 'time is ',$pstime;
?>

funny %T didn't work

could also be just
$pstime = strftime('%H:%M:%S',mktime() - 10800);
echo '<p>time is ',$pstime;

[coopster]

funny %T didn't work

I'll bet you ran that on a Windows box ;)

Another option, letting MySQL do the work:

SELECT TIME(NOW() - INTERVAL 3 HOUR) AS threeHoursAgo;

[figment88]

thanks guys, I was actually looking for the mysql version. Sorry didn't make it clear. We really should have a SQL forum.

also, there's the problem of people submitting at 2 in the morning. Just subtracting three hours gives a negative time. Maybe I should pre-process in php.

[edited by: figment88 at 8:00 pm (utc) on Sep. 22, 2005]

[coopster]

WebmasterWorld now has a Database [webmasterworld.com] forum. But it wasn't quite clear whether or not you wanted a PHP Server Side Solution or an SQL statement. Now we have both ;)

[coopster]

No, subtracting three hours from a DATETIME type of variable will not return a negative time value. For example:

SELECT TIME('2005-09-23 02:00:00' - INTERVAL 3 HOUR) AS threeHoursAgo; 
// Returns: 
+---------------+ 
¦ threeHoursAgo ¦ 
+---------------+ 
¦ 23:00:00      ¦ 
+---------------+

[jatar_k]

and doing it the way I did the timestamp will never get interpretted as a neg time either

[figment88]

ok, thanks again very helpful.