Timestamp and timezone confusion

[clay1]

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);

[btherl]

What happens when you use what you have now?

[sandeep529]

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

 

 

 

 

[clay1]

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

[sandeep529]

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

[btherl]

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.

[clay1]

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?

[btherl]

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)?