clay1 Posted December 29, 2011 Share Posted December 29, 2011 Hi, I am writing a script for invoicing various contractor jobs. I have fields for date assigned and date completed. Both are timestamp with timezone columns in a postgresql table My goal is to have a cron job which selects the records from the table on the dates the invoices are due and includes the proper jobs-- i.e. the ones that are completed prior to the due date and time(5PM on the first and 15th of each month) That is where I am running into confusion as to how to store the time/tz and then select it in the future so everything is correct This is what I have right now: $assignmenttype, "miles" => $miles, "notes" => $notes, "dateassigned" => "{$assignmenty1}-{$assignmentm1}-{$assignmentd1} $time1:00 America/Chicago", "datecompleted" => "{$completedassignmenty1}-{$completedassignmentm1}-{$completedd1} $time2:00 America/Chicago"); $res = pg_insert($conn, 'assignments', $invoicearray); Quote Link to comment https://forums.phpfreaks.com/topic/254032-timestamp-and-timezone-confusion/ Share on other sites More sharing options...
btherl Posted December 29, 2011 Share Posted December 29, 2011 What happens when you use what you have now? Quote Link to comment https://forums.phpfreaks.com/topic/254032-timestamp-and-timezone-confusion/#findComment-1302376 Share on other sites More sharing options...
sandeep529 Posted December 30, 2011 Share Posted December 30, 2011 Hi, The procedure I am outlining here is Just my Idea. I need someone to confirm its reliability... The best way to handle the situation would be to use Unix Timestamps. This is because unix timestamps are timezone independent. This means when some thing is to happen at a timestamp of X, then it has to happen when X seconds have passed since 1970-01-01 00:00:00 at greenwich. In otherwords servers running in different parts of the world running in different timezones always return the same unix timestamp. echo date_create('2011-12-30 00:00:00 GMT')->format('U');echo "<br/>"; echo date_create('2011-12-30 05:30:00 GMT+5:30')->format('U');echo "<br/>"; So if you want a user in timezone GMT+5:30 (India) to recieve a notification at say Jan 15 2012 00:00:00, you have to find what is the unix timestamp when a clock in india shows time Jan 15 2012 00:00:00; can be done as... $timestamp_one = date_create('2012-01-15 00:00:00 GMT+5:30')->format('U'); Insert that value in the database. You can also insert the value of unix timestamp when a clock in GMT timezone shows Jan 15 2012 00:00:00; can be done as $timestamp_two =gmmktime(0,0,0,1,15,2012); This is for handling the second one in the following situtations... For querying. Situtation 1: 1.Your server clock shows 2012-01-15 6:24:34 2.You have a user in database with notification timestamp_one, $user_notification_timestamp = 1326607200.Assume that this timestamp is a hour boundary like 5:00:00 ...and not like 5:34:34... 3. You have to check if the user has to be notified You have to do the following $my_last_hour_timestamp = mktime(6,0,0,1,15,2012); //returns the unix timestamp when your server clock was at 2012-01-15 6:00:00 if($my_last_hour_timestamp == $user_notification_timestamp) send_user_notification(); Situtation 2: 1.Your want to send a notification to all users with a notification time of 2012-01-25 9:00:00/their time right now.... 2.You have a user in database with notification timestamp_two ,$user_notification_timestamp_two = 1326607200.Assume that this timestamp is a hour boundary like 5:00:00 ...and not like 5:34:34... You have to do the following $timestamp = gmmktime(9,0,0,1,25,2012); //returns the unix timestamp when a C LOCK AT GMT is at 2012-01-25 9:00:00 if($timestamp == $user_notification_timestamp_two) send_user_notification(); I dont know if this is the best method..So You may want to wait till someone else reply.... Good Luck.... Quote Link to comment https://forums.phpfreaks.com/topic/254032-timestamp-and-timezone-confusion/#findComment-1302451 Share on other sites More sharing options...
clay1 Posted December 30, 2011 Author Share Posted December 30, 2011 What happens when you use what you have now? Right now I get an error because of the America/Chicago. If I remove that the values are stored as such: 2011-12-29 00:00:00+00 Sandpeep: Thanks for the help, but Postgresql does not support Unix timestamps Quote Link to comment https://forums.phpfreaks.com/topic/254032-timestamp-and-timezone-confusion/#findComment-1302657 Share on other sites More sharing options...
sandeep529 Posted December 31, 2011 Share Posted December 31, 2011 Sandpeep: Thanks for the help, but Postgresql does not support Unix timestamps As unix timestamps are just integers, you can use interget column to store a unixtimestamp.... Quote Link to comment https://forums.phpfreaks.com/topic/254032-timestamp-and-timezone-confusion/#findComment-1302796 Share on other sites More sharing options...
btherl Posted January 2, 2012 Share Posted January 2, 2012 Ok I think i'm clear on the problem now. Postgres stores timestamps with a GMT offset, not an english description of the timezone such as "America/Chicago". And it doesn't translate descriptions into offsets either. If you can work out yourself which timezone you are in and give it the offset, then that should work fine. Even if your time zone changes due to daylight savings, the timestamp you stored is still valid and will work with postgres arithmetic. It checks the timezone of "timestamp with time zone" data types and takes them into account. Quote Link to comment https://forums.phpfreaks.com/topic/254032-timestamp-and-timezone-confusion/#findComment-1303490 Share on other sites More sharing options...
clay1 Posted January 4, 2012 Author Share Posted January 4, 2012 Ok I think i'm clear on the problem now. Postgres stores timestamps with a GMT offset, not an english description of the timezone such as "America/Chicago". And it doesn't translate descriptions into offsets either. If you can work out yourself which timezone you are in and give it the offset, then that should work fine. Even if your time zone changes due to daylight savings, the timestamp you stored is still valid and will work with postgres arithmetic. It checks the timezone of "timestamp with time zone" data types and takes them into account. http://www.postgresql.org/docs/8.2/interactive/datatype-datetime.html#DATATYPE-TIMEZONES Says you can specify the full description and it will account for DST.. but I don't understand how and can't find any examples of this even though it seems like it should be a common thing? Quote Link to comment https://forums.phpfreaks.com/topic/254032-timestamp-and-timezone-confusion/#findComment-1304346 Share on other sites More sharing options...
btherl Posted January 10, 2012 Share Posted January 10, 2012 Ok it appears it does accept them. What is the error you get and what is the actual query you are running (after variable substitution)? Quote Link to comment https://forums.phpfreaks.com/topic/254032-timestamp-and-timezone-confusion/#findComment-1305994 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.