Link to home
Start Free TrialLog in
Avatar of courtenayt
courtenayt

asked on

java calendars, timezones & daylight saving

HI,

I'm trying to figure out how to correctly use Java calendars, timezones, etc. to calculate the correct information to put in my database.

Here is my situation:

1. A user enters a due date consisting of a Month, Day & Year via a web interface
2. To submit the due date to the database I need to convert the Month, Day & Year into a timestamp.
3. To accomplish this I was planning to do the following, but I'm not sure it's going to work between timezone changes, the confusing nature of java calendars & timezones, daylight saving issues and postgres timestamp conversion issues & it's own daylight saving issues:

Steps:
A: create a Java Calendar object (This is auto-initialized at my servers timezone US/Eastern and the current date/time the user submitted the due date).  Ex. The time calendar was created is: Jan 1, 2007 4pm EST

B. Set the timezone of the calendar to match the user's timezone Ex. use cal.setTimeZone(TimeZone.getTimeZone("US/Pacific")); thus making the calendar set to Jan 1, 2007 1pm PDT.

C. Set the month, day and year of the calendar object to match the due date submitted by the user.  Ex. cal.set("2008", "4", "20") thus making the calendar set to March 20, 2008 1pm PDT.

D. Then I need to change the due time to be at 2am of the next day (for expiration purposes) Ex. add a day: cal.roll(Calendar.DATE, true) then change the time to 2am: set(Calendar.HOUR_OF_DAY, 2)  thus making the calendar March 21, 2008 2am PDT.

E. Store the data in the database in a timestamp with timezone field (postgres DB).  The time is stored relative to UTC, but the timezone is (US/Eastern) which fluctuates between GMT-4 and GMT-5 depending on daylight saving.

F. Then I need a way to do a check on the database every couple of hours to determine if a due date is overdue.  Somehow I need to pull in the timestamp with timezone data back into the database and check it against the current time.  I believe I can do this by getting the text value from the database (stored as follows: 2007-01-05 13:48:49.232279-05) and then convert it into a calendar object set to US/Eastern timezone and also create a new calendar object with the current date/time and also in the US/Eastern timezone and mark the initial submission as overdue if I compare the calendars using the "after" function.

This all seems rather difficult and possible hiding some error in conversion I am not picking up.  

I've noticed that if I test some of these ideas using output to a JSP page, changing the timezone on the calendar doesn't seem to change the time or timezone when displaying <%= cal.getTime() %>, but from what I've read tis may be more of a display issue than a calendar issue.  I'm not clear at all what I need to do to display things correctly either.  

I had also thought of an alternate idea of storing the dates in a text field in the database to help cut down on some of the timezone/daylight saving issues, but don't know which is better.  This idea came from the fact that if a due date was set for over a year from now, the database wouldn't correctly convert future daylight savings, at least I'm pretty sure it won't.  Anyway....

If anyone can offer suggestions on if this works/how it can be improved/how to display, etc. I would be most greatful.  Thanks,
Courtenay

Avatar of mzalfres
mzalfres
Flag of Poland image
You can generate timestamp for desired time on the the client side using eg. JavaScript, shift it to UTC and then send to database. This way you don't need to handle timezones in your DB.

Another solution is to assign timezones to your users, if there aren't many.

You can also calculate not the timestamp, but difference from now to the selected time, and send it to the server. It will the re-calculate it back to the date, but within its own timezone.



Avatar of courtenayt
courtenayt

ASKER

Thanks for the ideas.  I was planning to add timezones to my users, that is how I would set the calendar to the users timezone.  Maybe I'm not following how you suggest using it differently.  I'm trying to avoid using javascript as my users will most likely be using old outdated browsers and/or have javascript disabled.

I'm interested in your idea of calculating the time difference, but I'm not sure how to go about that.  Could you give me an example with some code?

Thanks,
Courtenay
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image
use postgres backend to do timestamp arithmetic.
create table the_sla_table( id serial primary key, due timestamp with time zone );

insert into the_sla_table(id, due ) values( ?, current_timestamp + interval '1 day' )
returning *;
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image
insert into the_sla_table(id, due ) values( ?, tomorrow + time '02:00' ) returning *;

or if that converts it to UTC try.

insert into the_sla_table(id, due ) values( ?, tomorrow + time '02:00' at time zone 'America/Eastern') returning *;
Avatar of courtenayt
courtenayt

ASKER

Hi,

Thanks for the idea.  Since I have to have the date the user inserted through drop down menus of month, day, year from the webapp, I'm thinking I can convert that into a string in java.  Ex. 2008-04-08 02:00:00 PST (user's timezone) and then use postgres to add a day (so I don't have to worry about getting a date like January 32, 2008.  I'm assuming that Postgres accounts for things like leap years, etc. in the future is that correct?

Here is what I have working as an insert:
 insert into timestamp_test(id, timestamp ) values( 18, '2008-04-08 02:00:00 pst');

However, if I try adding a day, I get an error message.  Is there any way of adding a day to this?  If not, I'll just set it for 23:59:00 of the same date the user entered instead of moving it ahead a day.

insert into timestamp_test(id, timestamp ) values( 19, '2008-04-08 02:00:00 pst' + interval '1 day');
ERROR:  Bad interval external representation '2008-04-08 02:00:00 pst'

Also, I wasn't sure what the returning* was you had at the end of your insert statements.  It causes an error when I try to use it:

insert into timestamp_test(id, timestamp ) values( 19, '2008-04-08 02:00:00 pst') returning *;
ERROR:  parser: parse error at or near "returning" at character 83
 
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image
treacle=> select timestamp with timezone '2008-04-08 02:00:00 pst' + interval '1 day';
      ?column?
---------------------
 2008-04-09 02:00:00
(1 row)

select timestamp with time zone '2008-04-08 02:00:00 pst' + interval '1 day';
        ?column?
------------------------
 2008-04-09 11:00:00+01
(1 row)

returning * is a relatively new feature that allows you to see values that you have just inserted or updated, so you can retrieve them simply using a java resultset.

treacle=> insert into table_x values ( default, timezone(current_setting('TIMEZONE'), date_trunc( 'day', timestamp '2008-04-08 12:34:00') + interval '1 day' + time '02:00')) returning *
;
 id |          due
----+------------------------
  8 | 2008-04-09 02:00:00+01
(1 row)

INSERT 0 1
treacle=>
Avatar of courtenayt
courtenayt

ASKER

Hi,

The returning* must be in a newer version of postgres.  I'm still with the ancient 7.3 and trying to convince my hosting company to upgrade.

I tried
insert into timestamp_test values ( 20, timezone(current_setting('TIMEZONE'),
date_trunc( 'day', timestamp '2008-04-08 12:34:00') + interval '1 day' + time '02:00'));

but I received the following error message:
"ERROR:  Time zone 'unknown' not recognized"

I can't figure out where it would be coming from, but I'm hoping you have some idea.   Could it have to do wit the older version of postgres?

Thanks,
Courtenay
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image
Your CONNECTION - or "session" has no concept of its time zone.
you can set it using the following SQL statement.

SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT }

explicitly

SET SESSION TIME ZONE 'America/Easterm';

or preferably from the OS

SET SESSION TIME ZONE LOCAL;
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image
Your CONNECTION - or "session" has no concept of its time zone.
you can set it using the following SQL statement.

SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT }

explicitly

SET SESSION TIME ZONE 'America/Easterm';

or preferably obtained from the clients browser like

<script language="JavaScript">
<!--
var visitortime = new Date();
document.write('<input type="hidden" name="x-VisitorTimeZoneOffset" ');
if(visitortime) {
document.write('value="' + visitortime.getTimezoneOffset()/60 + '">');
}
else {
document.write('value="JavaScript not Date() enabled">');
}// -->
</script>
<noscript>
<input type="hidden" name="x-VisitorTimeZoneOffset"
value="Browser not JavaScript enabled">
</noscript>
Avatar of courtenayt
courtenayt

ASKER

If I were to use SET SESSION TIME ZONE 'America/Easterm';

1. How should I write is? I tried:

SET SESSION TIME ZONE 'america/Eastern';insert into timestamp_test values ( 20, timezone(current_setting('TIMEZONE'),date_trunc( 'day', timestamp '2008-04-08 12:34:00') + interval '1 day' + time '02:00'));

but it gives me this error:

SET
ERROR:  Time zone 'america/eastern' not recognized

2. What does this actually do?  Does it change the timezone for just the current insert statement or does it change the timezone for all inserts using timezone until it is SET again in the future?

3.  I would like to prevent changing the way the database interprets timezones.  I like what it does right now.  It takes whatever timestamp is inserted (in whatever timezone) and converts it to US/Eastern for me based on the difference to UTC.  At least this is what my understanding is.  If I use the SET timezone approach will this change that?

Thanks,
Courtenay




Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image
try
SET SESSION TIME ZONE 'US/Easterm';

It  changes the timexone for that database connection.

Tismestamps are stored as UTC ie Greenwich Mean Time.
Avatar of courtenayt
courtenayt

ASKER

Hi.  I ran the following:

teska_test=> SET SESSION TIME ZONE 'US/Easterm';
SET

so this seemed to work

then I ran the following and got an error:
teska_test=> insert into timestamp_test values ( 20, timezone(current_setting('TIMEZONE'),
teska_test(> date_trunc( 'day', timestamp '2008-04-08 12:34:00') + interval '1 day' + time '02:00'));
ERROR:  Time zone 'us/easterm' not recognized

I guess I'm also not sure what you mean by the timezone is set for the "connection"  to test things I am logging into my database directly, but from the web I am logging in to the database via connection pooling.  How will this impact the setting of the timezone?  Is a connection only for the duration of a query or does it last longer than that?

Thanks so much for your help!
Courtenay
Avatar of courtenayt
courtenayt

ASKER

Also, I don't know if this makes a difference, but I'm using prepared statements to do my inserts.
Avatar of courtenayt
courtenayt

ASKER

I just tried the following and got it to work:


teska_test=> insert into timestamp_test values (27, '2007-04-17 8:25:00' AT TIME ZONE INTERVAL '-04:00' + interval '1 day');

INSERT 306341 1

teska_test=> select * from timestamp_test where id = 27;                        
 id |       timestamp
----+------------------------
 27 | 2007-04-18 08:25:00-04

also, the timezone is working correctly when I use -)5:00 as the timezone.

teska_test=>  insert into timestamp_test values (28, '2007-04-17 8:25:00' AT TIME ZONE INTERVAL '-05:00' + interval '1 day');
INSERT 306342 1

teska_test=> select * from timestamp_test where id = 28;                        
id |       timestamp
----+------------------------
 28 | 2007-04-18 07:25:00-04

I've run into 2 issues.  If instead of using AT TIME ZONE INTERVAL '-04:00' I use AT TIME ZONE 'EST' the insert works, but postgres does not seem to account for current daylight saving time zone changes changes.  For example the following occurs:

teska_test=>  insert into timestamp_test values (29, '2007-04-17 8:25:00' AT TIME ZONE 'EST' + interval '1 day');
INSERT 306344 1

teska_test=> select * from timestamp_test where id = 29;                        
 id |       timestamp
----+------------------------
 29 | 2007-04-18 07:25:00-04

My understanding is that it should be inserting the following

teska_test=> select * from timestamp_test where id = 29;                        
 id |       timestamp
----+------------------------
 29 | 2007-04-18 08:25:00-04

since EST is currently -04:00 and after daylight saving ends in November it should return to -05:00.  Ideally I would like to be able to use the 'EST' version of the timezone so I don't have to manually change my insert code every time there is a change in daylight saving, but it seems that that it doesn't change it for me anyway.

Also, I tried adjusting the time using:

teska_test=> insert into timestamp_test values (30, '2007-04-17 8:25:00' AT TIME ZONE INTERVAL '-04:00' + interval '1 day' - time '06:25');
INSERT 306345 1

teska_test=> select * from timestamp_test where id = 30;                        
 id |       timestamp
----+------------------------
 30 | 2007-04-18 02:00:00-04

As you can see this works correctly to obtain 2:00 am as the time, but I had to use - time '06:25' to obtain it.  I did this by subtracting the time from the timestamp by 02:00 in my head.  I still need to figure out how I can have this subtraction done automatically.  I've looked through the documentation for postgres, but  the only way I can see to change the time is by adding or subtracting from it.  It would be nice if there was a way ti just say AT TIME 02:00.  Do you have any suggestions on how I might get the time to change correctly?

Thanks,
Courtenay




Avatar of courtenayt
courtenayt

ASKER

Actually I need to take something back.  I said the following was correct:

teska_test=>  insert into timestamp_test values (28, '2007-04-17 8:25:00' AT TIME ZONE INTERVAL '-05:00' + interval '1 day');
INSERT 306342 1

teska_test=> select * from timestamp_test where id = 28;                        
id |       timestamp
----+------------------------
 28 | 2007-04-18 07:25:00-04

but shouldn't it be the following:

teska_test=> select * from timestamp_test where id = 28;                        
id |       timestamp
----+------------------------
 28 | 2007-04-18 09:25:00-04

My understanding is it should add an hour when moving from -05 to -04 not subtract an hour.  This is really weird!

I'm also getting really confused over which timezone is what in UTC.  My understanding was that on daylight saving in the US at least that timezones shifted in relation UTC so standard time EST would be UTC -05:00 and EST in daylight saving EST would be UTC -04:00.  Maybe I'm not getting this straight, but I do know that my server is set to EST and after daylight saving began this spring, the inserted timestamps in my database switched from being displayed as -05:00 before DST to -04:00 after DST.
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image
I misspelt Easterm.

SET SESSION TIME ZONE 'US/Eastern';

use function timezone() ?

select
timezone('US/Eastern',
date_trunc( 'day', timestamp '2008-04-08 12:34:00') + interval '1 day' + time '02:00'));

Avatar of courtenayt
courtenayt

ASKER

I think I'm beginning to see why you wanted to SET the TIME ZONE first.  Because when using the AT TIME ZONE to insert, it takes the system timezone and converts it into which ever timezone I want, but what I'm looking for is to start with the users time zone and have the insert convert it to UTC of the timezone of my server (currently UTC -04:00).

OK, I've got it to work:

teska_test=> SET SESSION TIME ZONE 'PST';                                      
SET

teska_test=> insert into timestamp_test values (126, timezone(current_setting('TIMEZONE'), date_trunc('day',timestamp '2007-04-17 10:25:00') + interval '1 day' + time '02:00'));

This produces the following in the database:       
126    2007-04-18 06:00:00-04

Questions:

1. It appears that using PST is -08 and CST is -05 since it converted 02:00 PST to 06:00-04 and late converted 02:00 CST to 04:00-04.  Is this going to be a problem with changes in daylight saving?

2. I'm still not sure about how a 'database connection' is defined in relation to using SET TIME ZONE.  (I'm using database connection pooling and prepared insert statements if that makes a difference).  Also, do I need to put the SET TIME ZONE and the insert statement into some sort of transaction.  What if someone else is using SET timezone at the same time?  Can't this cause problems?

OK,
I'm done for now :)
Avatar of courtenayt
courtenayt

ASKER

Cool, it appears I don't need to set the timezone for the database connection.  Instead it works correctly if I use:

teska_test=> insert into timestamp_test values (128, timezone('CST', date_trunc('day',timestamp '2007-04-17 10:00:00') + interval '1 day' + time '02:00'));

resulting in:
128   2007-04-18 04:00:00-04

for some reason only the style CST works, but not 'US/Central' or 'US/Eastern'.  Could be a difference in postgres versions since I have 7.3.

Now my only remaining concern is Daylight Saving strangeness. (see #2 in my last post).

Thanks so much, it's really coming along!
Avatar of courtenayt
courtenayt

ASKER

Sorry, I meant see Question #1 about daylight savings, not #2!
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image
I think there was a bug with regard to timestamp with time zone in version 7.3
Not knowing what patches you may have applied makes it a bit difficult to ascertain what the problem is.  Also you need to check the Linux OS to make sure it has the correct DST rools.

See

man tzset

eg
% setenv TZ 'US/Eastern'
% date
Tue Apr 17 13:36:22 EDT 2007
% setenv TZ 'Europe/London'
% date
Tue Apr 17 18:38:15 BST 2007

Avatar of courtenayt
courtenayt

ASKER

Thanks.  I took a look at man tztest, but I'm not following how I check the settings.  I don't appear to have access to use % as I am using a shared hosted server and don't have full access to everything.  If I type date at the prompt it gives me the correct date and time for EST.

Do you know what I can/should do?
Avatar of courtenayt
courtenayt

ASKER

Here is what is listed for postgres : psql 7.3.18-RH I assume this is the same as the postgres version.
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image
the percent sign signifies the Unix command line prompt !
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image
treacle=# set session time zone 'US/Eastern';SET
treacle=# select twopm at time zone current_setting('TIMEZONE') as twopm, twopm as gmt from ( select timezone(current_setting('TIMEZONE'), date_trunc('day',timestamp '2007-04-17 10:25:00') + interval '1 day' + time '02:00') as twopm ) as test;
        twopm        |          gmt          
---------------------+------------------------
 2007-04-18 02:00:00 | 2007-04-18 02:00:00-04
(1 row)

treacle=# set session time zone 'Europe/London';SET
treacle=# select twopm at time zone current_setting('TIMEZONE') as twopm, twopm as gmt from ( select timezone(current_setting('TIMEZONE'), date_trunc('day',timestamp '2007-04-17 10:25:00') + interval '1 day' + time '02:00') as twopm ) as test;
        twopm        |          gmt          
---------------------+------------------------
 2007-04-18 02:00:00 | 2007-04-18 02:00:00+01
(1 row)
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image
that should have been labelled twoam of course.
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image
to test this set up a table with timestamps at the daylight saving time boundaries for a couple of different years and select from that rather than provide the timestamp string literal.
Avatar of courtenayt
courtenayt

ASKER

Hi,

Thanks for the ideas.  I tried the following:


teska_test=> set session time zone 'EST';                                       SET
teska_test=> select twopm at time zone current_setting('TIMEZONE') as twopm, twopm as gmt from ( select timezone(current_setting('TIMEZONE'), date_trunc('day',timestamp '2007-04-17 10:25:00') + interval '1 day' + time '02:00') as twopm ) as test;

and I got:


        twopm        |          gmt
---------------------+------------------------
 2007-04-18 02:00:00 | 2007-04-18 02:00:00-05
(1 row)

This seems to be wrong.  When you ran the same thing it said -04 for the relationship to gmt.

 I can only seem to get my DB to use the 3 letter versions of timezones (if I list places it lets me set it, but doesn't recognize the timezone when I run the next query).  Therefore, I have tried setting it to GMT and BST (Brittish summer time) to check it, but it isn't a very helpful test.  

teska_test=> set session time zone 'GMT';                                       SET
teska_test=> select twopm at time zone current_setting('TIMEZONE') as twopm, twopm as gmt from ( select timezone(current_setting('TIMEZONE'), date_trunc('day',timestamp '2007-04-17 10:25:00') + interval '1 day' + time '02:00') as twopm ) as test;
        twopm        |          gmt
---------------------+------------------------
 2007-04-18 02:00:00 | 2007-04-18 02:00:00+00
(1 row)

teska_test=> set session time zone 'BST';                                       SET
teska_test=> select twopm at time zone current_setting('TIMEZONE') as twopm, twopm as gmt from ( select timezone(current_setting('TIMEZONE'), date_trunc('day',timestamp '2007-04-17 10:25:00') + interval '1 day' + time '02:00') as twopm ) as test;
        twopm        |          gmt
---------------------+------------------------
 2007-04-18 02:00:00 | 2007-04-18 01:00:00+00
(1 row)


It seems like it is either something wrong with the settings on my database or a problem with the database code in 7.3.  Do you have any ideas?

I'm hoping you can explain how I would pull the timezone from a table from the queries you gave me.  Could you give me an example query from a table called timestamp_test2 with a column called date that holds the timestamps of dates I insert before and after daylight saving times?

Thanks,
Courtenay
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image
BAD BAD Idea giving a table a column called date because that is a SQL keyword.  You have to double quote the column name where you use it.

select twoam at time zone current_setting('TIMEZONE') as twoam, twoam as gmt from ( select timezone(current_setting('TIMEZONE'), date_trunc('day',timestamp_test2."date") + interval '1 day' + time '02:00') as twoam from timestamp_test2 ) as test;

treacle=> create table timestamp_test2( id serial primary key, "date" timestamp );
NOTICE:  CREATE TABLE will create implicit sequence "timestamp_test2_id_seq" for serial column "timestamp_test2.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "timestamp_test2_pkey" for table "timestamp_test2"
CREATE TABLE
treacle=> insert into timestamp_test2 values ( default, now() );
INSERT 0 1
treacle=> select twoam at time zone current_setting('TIMEZONE') as twoam, twoam as gmt from ( select timezone(current_setting('TIMEZONE'), date_trunc('day',timestamp_test2."date") + interval '1 day' + time '02:00') as twoam from timestamp_test2 ) as test;
        twoam        |          gmt          
---------------------+------------------------
 2007-04-21 02:00:00 | 2007-04-21 02:00:00+01
(1 row)

Generally you must use the geographical timezone to get daylight savings time to work properly.
If that does not work because of a bug in PostgreSQL then I would upversion to get rid of the problem.  Time to set up your own linux server ?
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image
Probablly better to explicitly convert reference to 'GMT'

select twoam at time zone current_setting('TIMEZONE') as twoam, twoam at time zone 'GMT' as gmt from ( select timezone(current_setting('TIMEZONE'), date_trunc('day',timestamp_test2."date") + interval '1 day' + time '02:00') as twoam from timestamp_test2 ) as test;
Avatar of courtenayt
courtenayt

ASKER

I guess I'm just getting lost.  I'm not sure what we are trying to do by creating this table of dates.  From your example the timestamp is without a timezone, but the select statement you ended up with one.   Is this a problem with my database or should I change the field in timestamp_test2 to include the timezone?

Here is what I get right now (without using timezone in the DB field):

teska_test=> create table timestamp_test2( id serial primary key, date_test timestamp );
NOTICE:  CREATE TABLE will create implicit sequence 'timestamp_test2_id_seq' for SERIAL column 'timestamp_test2.id'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'timestamp_test2_pkey' for table 'timestamp_test2'
CREATE TABLE

teska_test=> insert into timestamp_test2 values ( default, now() );
INSERT 306426 1
teska_test=> select * from timestamp_test2;
 id |         date_test
----+----------------------------
  1 | 2007-04-23 13:04:53.684882
(1 row)


teska_test=> select twoam at time zone current_setting('TIMEZONE') as twoam, twoam at time zone 'GMT' as gmt from ( select timezone(current_setting('TIMEZONE'), date_trunc('day',timestamp_test2.date_test) + interval '1 day' + time '02:00') as twoam from timestamp_test2 ) as test;
ERROR:  Time zone 'unknown' not recognized

Avatar of courtenayt
courtenayt

ASKER

I'd love to have the time & resources & expertise to set up my own Linux server, but unfortunately it's just not an option right now.  I'm working on my hosting company to upgrade to the latest postgres.  
Good idea though!

Thanks,
Courtenay
Avatar of courtenayt
courtenayt

ASKER

I just wanted to say how much I appreciate all of your help - you have been completely invaluable in helping me solve this problem!  Hopefully my hosting company will update the database soon.  I've put in another note to them!

Thanks!
Courtenay
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image
set session time zone 'US/Eastern';
Avatar of courtenayt
courtenayt

ASKER

OK, I set the timezone and ran the query again.  Here is what I got:


        twoam                 |         gmt
--------------------------+---------------------
 2007-04-24 02:00:00 | 2007-04-24 07:00:00

I'm not sure what this is telling me.  I am assuming it is saying twoam is EST (-5) and gmt is (0).

I tried entering dates around past and future daylight saving changes and here is what I got:


        twoam                |         gmt
--------------------------+---------------------
 2003-05-05 02:00:00 | 2003-05-05 07:00:00
 2003-05-08 02:00:00 | 2003-05-08 07:00:00
 2006-10-21 02:00:00 | 2006-10-21 07:00:00
 2006-10-31 02:00:00 | 2006-10-31 07:00:00
 2008-03-08 02:00:00 | 2008-03-08 07:00:00
 2008-03-11 02:00:00 | 2008-03-11 07:00:00

it doesn't appear to change anything.

Courtenay
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image
$ psql -e treacle
Welcome to psql 8.2.1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

treacle=> \i x.psql
set session time zone 'US/Eastern';
SET
select '2003-05-05 02:00:00' at time zone 'GMT';
      timezone      
---------------------
 2003-05-05 06:00:00
(1 row)

select '2003-05-08 02:00:00' at time zone 'GMT';
      timezone      
---------------------
 2003-05-08 06:00:00
(1 row)

select '2006-10-21 02:00:00' at time zone 'GMT';
      timezone      
---------------------
 2006-10-21 06:00:00
(1 row)

select '2006-10-31 02:00:00' at time zone 'GMT';
      timezone      
---------------------
 2006-10-31 07:00:00
(1 row)

select '2008-03-08 02:00:00' at time zone 'GMT';
      timezone      
---------------------
 2008-03-08 07:00:00
(1 row)

select '2008-03-11 02:00:00' at time zone 'GMT';
      timezone      
---------------------
 2008-03-11 06:00:00
(1 row)
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image
psql -e treacle
Welcome to psql 8.2.1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

treacle=> \i x.psql
set session time zone 'US/Eastern';
SET
select ( '2003-05-05 02:00:00' at time zone current_setting('TIMEZONE') ) at time zone 'GMT';
        timezone        
------------------------
 2003-05-04 22:00:00-04
(1 row)

select ( '2003-05-08 02:00:00' at time zone current_setting('TIMEZONE') ) at time zone 'GMT';
        timezone        
------------------------
 2003-05-07 22:00:00-04
(1 row)

select ( '2006-10-21 02:00:00' at time zone current_setting('TIMEZONE') ) at time zone 'GMT';
        timezone        
------------------------
 2006-10-20 22:00:00-04
(1 row)

select ( '2006-10-31 02:00:00' at time zone current_setting('TIMEZONE') ) at time zone 'GMT';
        timezone        
------------------------
 2006-10-30 21:00:00-05
(1 row)

select ( '2008-03-08 02:00:00' at time zone current_setting('TIMEZONE') ) at time zone 'GMT';
        timezone        
------------------------
 2008-03-07 21:00:00-05
(1 row)

select ( '2008-03-11 02:00:00' at time zone current_setting('TIMEZONE') ) at time zone 'GMT';
        timezone        
------------------------
 2008-03-10 22:00:00-04
(1 row)
Avatar of courtenayt
courtenayt

ASKER

Hi,

I guess I'm not following what your last 2 posts are doing.  Could you explain what it is testing?

Thanks,
Courtenay
ASKER CERTIFIED SOLUTION
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of courtenayt
courtenayt

ASKER

I've written to my hosting company again.  They said they are going to "try" to upgrade postgres by Friday.  I don't know why they think there will be so many problems.  They said "If you are needing 8.x , we can schedule an upgrade attempt in the future (attempt mainly because this is a live server whereas the servers that have 8 are built with version 8) "   

Thanks,
Courtenay
Avatar of courtenayt
courtenayt

ASKER

Hi,

another update.  They haven't resolved the update to 8.1 yet, but think they have found a way.  Sorry for the delay on this question!

Courtenay
Avatar of courtenayt
courtenayt

ASKER

Hi,

just another quick update to keep the ticket open, I've been away on a major family emergency and am getting back to this asap.  Sorry for the delay.  Still waiting on the 8.1.  They tried an upgrade, but it caused all sorts of problems - arg!
Avatar of courtenayt
courtenayt

ASKER

Hi, please keep this ticket open.  I am still trying to get my database issues worked out to test the solution and I want to make sure I can give the points to earthman2 as soon as I can get it all working.  
Avatar of Venabili
Venabili
Flag of Bulgaria image
Any news here?
Avatar of courtenayt
courtenayt

ASKER

Still working with the hosting company.  We've run into problems we are trying to fix with the upgrade to the new database.  I'll keep you posted as I learn more.

Sorry!
Avatar of courtenayt
courtenayt

ASKER

Hi,

just a note to let you know my hosting company has made the update to the database - turns out they had to go to Postres and have a custom updater made for their servers.  They have worked really hard on this.  They still need to update the account I have this database running on, change some of my db access code to work with the new database upgrade and then  I can test this fully.  I'll keep you updated as I know more.  Again - so sorry for the delay - I haven't forgot and I will be giving you your points as soon as I can finish testing it.  I had no idea this was going to take so long to try out!
Avatar of courtenayt
courtenayt

ASKER

Hi,

I'm updating this again.  I've got only one server running the new database and really need my other one up to do full testing with my code.

In the mean time I'm trying to get back to this so I can finally give you some points!

I've been reading about prepared Java prepared statements.  I'm using the set statements in Java to set the rest of my data and noticed the following to setTimestamp() functions:

setTimestamp
public void setTimestamp(int parameterIndex,
                         Timestamp x)
                  throws SQLException

    Sets the designated parameter to the given java.sql.Timestamp value. The driver converts this to an SQL TIMESTAMP value when it sends it to the database.

    Parameters:
        parameterIndex - the first parameter is 1, the second is 2, ...
        x - the parameter value
    Throws:
        SQLException - if a database access error occurs

and

setTimestamp
public void setTimestamp(int parameterIndex, Timestamp x, Calendar cal)
                  throws SQLException

    Sets the designated parameter to the given java.sql.Timestamp value, using the given Calendar object. The driver uses the Calendar object to construct an SQL TIMESTAMP value, which the driver then sends to the database. With a Calendar object, the driver can calculate the timestamp taking into account a custom timezone. If no Calendar object is specified, the driver uses the default timezone, which is that of the virtual machine running the application.

    Parameters:
        parameterIndex - the first parameter is 1, the second is 2, ...
        x - the parameter value
        cal - the Calendar object the driver will use to construct the timestamp
    Throws:
        SQLException - if a database access error occurs
    Since:
        1.2

I'm interested in the second function using the java Calendar object to set the timezone.  Do you have any experience using this function?  It seems like I can just leave my database the way it is (it inserts timestamps in EST(server time) relative to UTC).  When I use the setTimestamp(int parameterIndex, Timestamp x, Calendar cal) I have the calendar object set to the timezone of the user and the timestamp pre-set to whatever time I want. I assume thi will just adjust the time inserted to be the equivalent of the Calendar timezone, but in EST relative to UTC.  (Hope that sentence makes sense :)

This approach doesn't really allow me to use the database to change the time to what I want it to, but hopefully I can alter the timestamp in java first.  Too bad it doesn't just take a calendar object for the date, time & timezone instead of needing both the calendar and timstamp objects.  It seems a lot easier to modify Java calendar objects than Java timestamps, but i haven't really looked into it that much.

What do you think? It seems I am going to have to use some form of setTimestamp to get the data into the database anyway.

Thanks again!
Courtenay
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image
If you do this then you are trusting that the client systems to have the correct DST rules installed.
Avatar of courtenayt
courtenayt

ASKER

I thought that using this system  my server would be the one that needed the correct DST rules set, not the client systems.    I'm not sure how the client side would be a part of the DST rules beyond the user setting their timezone through the UI of my program.  The calendar objects would be created in my java bean code, not in my JSP.  Could you explain how the client side affects the DST rules and timezone?

I've also kind of gone back to a previous idea I had of using a java calendar initialized to my server timezone, then converting it to the timezone selected by the user via my UI, updating the calendar to whatever date a user selected off of the calendar in my UI then setting the time based on pre-defined parameter stored i my system (like 11:59 pm).  I have found, at least in postgres 7.3 that I can just insert that I can insert string representations of timestamps with timezones into my database.  This should convert it to the equivalent of UTC displayed in EST so I can then run periodic checks of the data to see if cerrtian dates have been passed and send out a warning to the users via email (or whatever).

I think this system should be ok.   I can't fully test it in postgres 8.2 until they finish converting my dev server over so I can try my code there.   I can't see that this system would involve relying on the client's own DST rules, but if you can see any potential problems with this, please let me know.  I'm thinking I would like to avoid having to constantly change the timezone in the database because there could potentially be many timestamp entries at one time and I don't want to slow down the database or potentially run into problems with timezones being changed by another insert before previous ones have completed (if that is even an issue).

Thanks again!
Courtenay
Avatar of courtenayt
courtenayt

ASKER

Thanks for all of you help on this - I ended up finally getting the upgrade to 8.1 and it made all of the difference.  I'm still working out some of the timezone issues, but your help has been really great.

Thanks,
Courtenay