Thread: timestamps cannot be created without time zones
Rainer Mager (rmager@vgkk.com) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
timestamps cannot be created without time zones
Long Description
Creating a TABLE with a TIMESTAMP type seems to ignore the WITH TIME ZONE option. That is, with or without the option
theTIMESTAMP still has a time zone.
Sample Code
vgkk=> create table a ( t1 timestamp, t2 timestamp with time zone, t3 timestamp without time zone );
CREATE
vgkk=> \d a
Table "a"
Attribute | Type | Modifier
-----------+--------------------------+----------
t1 | timestamp with time zone |
t2 | timestamp with time zone |
t3 | timestamp with time zone |
No file was uploaded with this report
> Rainer Mager (rmager@vgkk.com) reports a bug with a severity of 2 > Creating a TABLE with a TIMESTAMP type seems to ignore the WITH TIME > ZONE option. That is, with or without the option the TIMESTAMP still > has a time zone. We feel that the SQL timestamp definition as regards with/without time zone is undesirable. Our timestamp type always stores the time in UTC and prints it out according to the local time zone. The fact that the type then comes out as 'timestamp with time zone' is disputed, but they are in fact one and the same type. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Thanks for the info. I'm unsure on a few things, though. The Postgres docs mention the the precision/range of timestamp with and withount a timezone is different. Are you saying that, because they are, internally, the same the precision/range is also the same? Also, we have seen a bug regarding time stamps when they are set via JDBC. We thought that it might be a core Postgres bug but after playing with psql a bit I think it may be in the JDBC driver. What happens is that when a timestamp is written to the database, if the timestamp is earlier than 1901 (I don't know the exact cutoff date), the the time is munged at some point. Via psql I verified that this date is important: 1901-08-08 01:00:00 1902-08-08 01:00:00+09 Notice that the 1901 date looses the timezone. Via JDBC, these older date are not properly handled athe the +09 hours of the timezone are not properly accounted for. Specifically, if I insert (via JDBC) '1-Jan-1900 :00:00:00' and then select it back out I get '31-Dec-1900:15:00:00'. This is not a good thing. Should I enter a new bug? Thanks, --Rainer > -----Original Message----- > From: pgsql-bugs-owner@postgresql.org > [mailto:pgsql-bugs-owner@postgresql.org]On Behalf Of Peter Eisentraut > Sent: Friday, August 24, 2001 12:15 AM > To: rmager@vgkk.com; pgsql-bugs@postgresql.org > Subject: Re: [BUGS] timestamps cannot be created without time zones > > > > Rainer Mager (rmager@vgkk.com) reports a bug with a severity of 2 > > > Creating a TABLE with a TIMESTAMP type seems to ignore the WITH TIME > > ZONE option. That is, with or without the option the TIMESTAMP still > > has a time zone. > > We feel that the SQL timestamp definition as regards with/without time > zone is undesirable. Our timestamp type always stores the time in UTC and > prints it out according to the local time zone. The fact that the type > then comes out as 'timestamp with time zone' is disputed, but they are in > fact one and the same type. > > -- > Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
"Rainer Mager" <rmager@vgkk.com> writes:
> The Postgres docs mention the the precision/range of timestamp with and
> withount a timezone is different.
Where are you reading that, exactly? Since there isn't any difference
between "timestamp with and without a timezone", that can't possibly be
what was meant --- so we need to fix/clarify the documentation.
Please tell us where.
> What happens is that when a timestamp is written to the database, if the
> timestamp is earlier than 1901 (I don't know the exact cutoff date), the the
> time is munged at some point.
Timezone handling in PG is dependent on the underlying OS' timezone
database, which doesn't go back further than 1901 in any Unix that
I've heard of. You should realize of course that the very notion of
timezone was only standardized in the 1800s, so attaching a timezone
to dates much older than that would be a dubious practice anyway...
regards, tom lane
Tom Lane writes: > Timezone handling in PG is dependent on the underlying OS' timezone > database, which doesn't go back further than 1901 in any Unix that > I've heard of. You should realize of course that the very notion of > timezone was only standardized in the 1800s, so attaching a timezone > to dates much older than that would be a dubious practice anyway... Thus Rainer's point is that when having times both before and after 1901 in the same data set you get inconsistencies. This seems like a good reason to introduce a true 'timestamp without time zone' type. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
> Tom Lane writes: > > > Timezone handling in PG is dependent on the underlying OS' timezone > > database, which doesn't go back further than 1901 in any Unix that > > I've heard of. You should realize of course that the very notion of > > timezone was only standardized in the 1800s, so attaching a timezone > > to dates much older than that would be a dubious practice anyway... > > Thus Rainer's point is that when having times both before and after 1901 > in the same data set you get inconsistencies. This seems like a good > reason to introduce a true 'timestamp without time zone' type. Let me see if I follow here. If I am in the Eastern timezone and enter a time for 9pm, 1/1/1850, and someone else in the Central timezone enters the same time, if I look at the two dates from the Eastern timezone I will see mine as 9pm and the other as 10pm? Wow, I wonder if that is bad? It seems I would mix these in a table so it is the underlying data representation that may be the problem. Yikes. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian writes: > Let me see if I follow here. If I am in the Eastern timezone and enter > a time for 9pm, 1/1/1850, and someone else in the Central timezone > enters the same time, if I look at the two dates from the Eastern > timezone I will see mine as 9pm and the other as 10pm? That's exactly what happens, only that the central time would probably show 8pm. > Wow, I wonder if that is bad? Depends on the application. I do wonder how the backend gets to know the time zone from the front end. export PGTZ seems to do that, but not export TZ, which is the standard variable. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Peter Eisentraut <peter_e@gmx.net> writes:
> Bruce Momjian writes:
>> Let me see if I follow here. If I am in the Eastern timezone and enter
>> a time for 9pm, 1/1/1850, and someone else in the Central timezone
>> enters the same time, if I look at the two dates from the Eastern
>> timezone I will see mine as 9pm and the other as 10pm?
> That's exactly what happens, only that the central time would probably
> show 8pm.
No, that is *not* what happens. For dates outside the range known to
the local platform's timezone database, we effectively assume that all
times are given in GMT; no timezone corrections are applied on either
input or output. For example:
regression=# create table history(f1 timestamp);
CREATE
regression=# show TimeZone ;
NOTICE: Time zone is EST5EDT
SHOW VARIABLE
regression=# insert into history values(now());
INSERT 925967 1
regression=# insert into history values('1810-01-01 12:00');
INSERT 925968 1
regression=# select * from history;
f1
------------------------
2001-08-24 15:39:45-04
1810-01-01 12:00:00
(2 rows)
regression=# set TimeZone TO 'pst8pdt';
SET VARIABLE
regression=# select * from history;
f1
------------------------
2001-08-24 12:39:45-07
1810-01-01 12:00:00
(2 rows)
regression=#
The fact that such times are taken as GMT can be proven with some
experiments near the boundary, eg (back to EST timezone here for
display):
regression=# select '1901-12-14 1:00 gmt'::timestamp;
?column?
------------------------
1901-12-13 20:00:00-05
(1 row)
regression=# select '1901-12-14 1:00 gmt'::timestamp - '1 hour'::interval;
?column?
------------------------
1901-12-13 19:00:00-05
(1 row)
regression=# select '1901-12-14 1:00 gmt'::timestamp - '2 hour'::interval;
?column?
---------------------
1901-12-13 23:00:00
(1 row)
regression=#
While this is a little disconcerting, I am not sure how we can do
any better. Certainly adding or subtracting the current local timezone
offset wouldn't be an improvement (in this example, I'd certainly not
want to suppose that EDT -04 offset should apply to a wintertime date
in 1901, even if I was willing to assume that Eastern time was okay
otherwise).
What it comes down to is that our timestamp datatype *does* have
"timestamp without timezone" behavior for dates outside the range of
known timezone data.
regards, tom lane
> Peter Eisentraut <peter_e@gmx.net> writes: > > Bruce Momjian writes: > >> Let me see if I follow here. If I am in the Eastern timezone and enter > >> a time for 9pm, 1/1/1850, and someone else in the Central timezone > >> enters the same time, if I look at the two dates from the Eastern > >> timezone I will see mine as 9pm and the other as 10pm? > > > That's exactly what happens, only that the central time would probably > > show 8pm. > > No, that is *not* what happens. For dates outside the range known to > the local platform's timezone database, we effectively assume that all > times are given in GMT; no timezone corrections are applied on either > input or output. For example: Nifty trick. :-) -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Tom Lane writes: > No, that is *not* what happens. For dates outside the range known to > the local platform's timezone database, we effectively assume that all > times are given in GMT; no timezone corrections are applied on either > input or output. Oh, I wasn't sure whether Bruce meant the behaviour in 1850 exactly. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Ok, I now understand more about how Postgres handles these older dates but we're still seeing a problem that I'm not sure how to handle. Simply put, we write a TIMESTAMP via JDBC and then read it back. What we write and what we read are different. The only way I can think of fixing the problem is having a check, in our code, to see if the dates are earlier than Postgres' magic cutoff day, and, if so, do timezone fixes. This is bad. Is there a better way. Is there, perhaps, a bug in the JDBC driver? Thanks, --Rainer
Hi all,
Sorry to reopen this issue but I still think there is a bug somewhere,
perhaps in the JDBC driver. The code and the end of this message
demonstrates the bug. Basically I write a timestamp to the database and then
read it back and what I write and what I get back are different. I don't
see how I can progrmatically make this correct in a consistent way without
knowing the "magic" dates in Postgres. Note that I believe there are more
than just one magic date. Apparently at ever older date (around 10,000 BC I
believe) the seconds are dropped.
The output from the code is (the computer's time was 03:23:49):
1850-Jan-01 03:23:49 JST
1850-Jan-01 06:23:49 JST
Thanks,
--Rainer
SimpleDateFormat format = new SimpleDateFormat( "yyyy-MMM-dd hh:mm:ss zz" );
Calendar cal = new GregorianCalendar();
cal.set( 1850, 00, 01 );
java.util.Date date = cal.getTime();
System.out.println( format.format( date ) );
try {
PreparedStatement ps = con.prepareStatement(
"update cust_prop_date set value = ? where customer_id = 8791"
);
ps.setTimestamp( 1, new Timestamp( date.getTime() ) );
ps.execute();
ps.close();
ps = con.prepareStatement( "select value from cust_prop_date where
customer_id=8791" );
ResultSet rs = ps.executeQuery();
rs.next();
date = new java.util.Date( rs.getTimestamp( "value" ).getTime() );
rs.close();
ps.close();
} catch( Exception e ) {
}
System.out.println( format.format( date ) );
I posted this about 2 weeks ago and saw no further follow ups. Is this
timestamp thing not considered a bug? Or am I just doing something wrong?
Thanks,
--Rainer
> -----Original Message-----
> Sorry to reopen this issue but I still think there is a bug
> somewhere,
> perhaps in the JDBC driver. The code and the end of this message
> demonstrates the bug. Basically I write a timestamp to the
> database and then
> read it back and what I write and what I get back are different. I don't
> see how I can progrmatically make this correct in a consistent way without
> knowing the "magic" dates in Postgres. Note that I believe there are more
> than just one magic date. Apparently at ever older date (around
> 10,000 BC I
> believe) the seconds are dropped.
> The output from the code is (the computer's time was 03:23:49):
>
> 1850-Jan-01 03:23:49 JST
> 1850-Jan-01 06:23:49 JST
>
> Thanks,
>
> --Rainer
>
>
> SimpleDateFormat format = new SimpleDateFormat( "yyyy-MMM-dd
> hh:mm:ss zz" );
> Calendar cal = new GregorianCalendar();
> cal.set( 1850, 00, 01 );
> java.util.Date date = cal.getTime();
> System.out.println( format.format( date ) );
> try {
> PreparedStatement ps = con.prepareStatement(
> "update cust_prop_date set value = ? where
> customer_id = 8791"
> );
> ps.setTimestamp( 1, new Timestamp( date.getTime() ) );
> ps.execute();
> ps.close();
>
> ps = con.prepareStatement( "select value from cust_prop_date where
> customer_id=8791" );
> ResultSet rs = ps.executeQuery();
> rs.next();
> date = new java.util.Date( rs.getTimestamp( "value" ).getTime() );
> rs.close();
> ps.close();
> } catch( Exception e ) {
> }
> System.out.println( format.format( date ) );
"Rainer Mager" <rmager@vgkk.com> writes:
>> Apparently at ever older date (around
>> 10,000 BC I
>> believe) the seconds are dropped.
You do realize that timestamps are floating point seconds relative to AD
2000, and so the accuracy decreases as you get further away from current
time?
>> The output from the code is (the computer's time was 03:23:49):
>>
>> 1850-Jan-01 03:23:49 JST
>> 1850-Jan-01 06:23:49 JST
I don't believe that Postgres will associate any timezone at all with
timestamps outside the range of the underlying OS' timezone database.
I get just
regression=# select '1850-Jan-01 03:23:49'::timestamp;
?column?
---------------------
1850-01-01 03:23:49
(1 row)
I'd say the problem here is on the Java side: something on the client
side is inappropriately attaching a timezone to a value that should not
have one. Possibly you should take this up on pgsql-jdbc; or perhaps
it's a problem with the Java datetime datatypes you are using.
regards, tom lane