Maybe a dumb question on timezones

Hello all,

I have confused myself to a point where I need to double check this with you all. Sorry if this is really a dumb question…

Okay - I have a web application that needs to support timezones. I provide a calendar widget that allows the user to set their timezone then select the date and hours/minutes for some event.

I then store that value in a record as an int by using:
$date_time comes in this format ‘YYYY-MM-DD H:S’
$time_zone is what they selected eg: America/Jamaica


$dtzone = new DateTimeZone($time_zone);
$dtime = new DateTime($date_time, $dtzone);
$timestamp = $dtime->format('U');
return $timestamp;

Now I store the int returned in the database.

I then have a PHP script that I execute via CRON which runs within a server in Sydney - Australia.

My understanding is that all my script needs to do is get the servers current time stamp via time() then search the database for any integers with the exact same integer produced by the time() call. I don’t have to worry about what timezone the user used to select the date/time.

Note that the CRON must run every minute as I allowed the user to select the minute as well.

Is this assumption correct?

Cheers

Marc

Yes, I think that should be fine. You are converting everything to unix time so they are all in the same “time zone” effectively, including time() on server in any timezone.

Hello Hash,

Thanks for your reply. I was reading all kinds of articles about offsets and GMT/UTC which is really messing with my head. I assumed that there could only be one time stream from the 1970 Unix start date and all of us all over the world use the same time stream - so an integer stamp is a representation of the time from 1970 Unix time…

It’s the number of seconds since midnight GMT, so yes there can be only one number of seconds since then.

Hello again,

I was wondering if anyone knows how to deal with the following - continuing on with my timestamp saga.

When the timestamp of the users selected date/hour/minute is saved to the database the seconds are set at 00 - I assume 00 as when I convert the timestamp back to date format the seconds are ‘00’.

Thus I can have a timestamp of 1262746260.

Looking at the linux cron logs I can see the CRON actually runs at 1 sec after the minute. So when the current timestamp is generated on the server for the compare search the integer can be at least 1 second off - thus it never finds any timestamp matches!

Is there a format for mySQL that states something like this:

“Find all records with the timestamp of ‘timestamp’ within the say 10 seconds”

This will allow my CRON to locate records even though the timestamp generated for the search is not exactly at 1 minute and zero seconds.

Cheers

Marc

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between

You could also mark the events as done and just select all where time < now and not done. This would ensure all get done if say the server went down for 2 mins, but you might not want that I guess.

mysql has many time and date functions.
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

The timediff function seems like it might help you out here.

That works with date time formats not unix timestamps as far as I’m aware.

Thanks Hash and pmw57 for your help!

Since I am comparing integers I will try this out


time_stamp_offset = $time_stamp + 10; // add 10 seconds to look 10 sections from now
    	
$whereStr = ' WHERE (datetime >= '.$time_stamp. ' AND datetime <= '.$time_stamp_offset.')

datetime being the field which holds the integer.

Thanks Hash! That worked!

That’s why there’s UNIX_TIMESTAMP and FROM_UNIXTIME functions to convert both ways :slight_smile:

Indeed :slight_smile: just don’t see the point of converting to time when you just want an integer ± 10