Обсуждение: time and timetz : Do I miss something?
Hello, i was just testing this against a variety of OSs/pgsql vesrions (from 8.4-701 JDBC 3 to 9.0-801 JDBC 4)
across both FreeBSD (6.1 -> 8-2) and Linux SLES 10.
This is the current time zone (EEST=UTC+3) (local Greek time zone):
postgres@smadevnew:~> date
Tue Apr 5 17:33:10 EEST 2011
This is the table:
dynacom=# \d testts
Table "public.testts"
Column | Type | Modifiers
--------------+-----------------------------+-----------
adate | date |
atime | time without time zone |
atimestamp | timestamp without time zone |
atimetz | time with time zone |
atimestamptz | timestamp with time zone |
dynacom=# SELECT * from testts;
adate | atime | atimestamp | atimetz | atimestamptz
------------+----------+---------------------+-------------+------------------------
2011-04-05 | 00:00:00 | 2011-04-05 00:00:00 | 00:00:00+03 | 2011-04-05 00:00:00+03
(1 row)
This is what the following code gives:
Class Classdriver = Class.forName("org.postgresql.Driver");
java.sql.Driver driver=(java.sql.Driver)Classdriver.newInstance();
out.println(driver.getMajorVersion()+"."+driver.getMinorVersion()+"<BR>");
out.println("Zone Offset millis="+Calendar.getInstance().getTimeZone().getRawOffset()+"<BR>");
out.println("DST millis="+Calendar.getInstance().getTimeZone().getDSTSavings()+"<BR>");
st = con.prepareStatement("SELECT adate,atime,atimestamp,atimetz,atimestamptz FROM testts");
rs = st.executeQuery();
if (!rs.next()) {
rs.close();
st.close();
throw new Exception("data is supposed to be there");
}
java.sql.Date sqldate_adate = rs.getDate(1);
out.println("sqldate_adate="+sqldate_adate+"<BR>");
SimpleDateFormat simpleDateFormatGTZ = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S Z");
SimpleDateFormat simpleDateFormatGMT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S Z");
simpleDateFormatGMT.setTimeZone(TimeZone.getTimeZone("GMT"));
java.sql.Timestamp atimestamp = rs.getTimestamp(3);
out.println("atimestamp="+atimestamp+"<BR>");
out.println("atimestamp millis="+atimestamp.getTime()+"<BR>");
out.println("atimestamp GRK format="+simpleDateFormatGTZ.format(atimestamp)+"<BR>");
out.println("atimestamp GMT format="+simpleDateFormatGMT.format(atimestamp)+"<BR>");
java.sql.Timestamp atimestamptz = rs.getTimestamp(5);
out.println("atimestamptz="+atimestamptz+"<BR>");
out.println("atimestamptz millis="+atimestamptz.getTime()+"<BR>");
out.println("atimestamptz GRK format="+simpleDateFormatGTZ.format(atimestamptz)+"<BR>");
out.println("atimestamptz GMT format="+simpleDateFormatGMT.format(atimestamptz)+"<BR>");
java.sql.Time atime = rs.getTime(2);
out.println("atime="+atime+"<BR>");
out.println("atime millis="+atime.getTime()+"<BR>");
out.println("atime in hours="+((double)atime.getTime()/(1000*3600))+"<BR>");
java.sql.Time atimetz = rs.getTime(4);
out.println("atimetz="+atimetz+"<BR>");
out.println("atimetz millis="+atimetz.getTime()+"<BR>");
out.println("atimetz in hours="+((double)atimetz.getTime()/(1000*3600))+"<BR>");
produces:
9.0
Zone Offset millis=7200000
DST millis=3600000
sqldate_adate=2011-04-05
atimestamp=2011-04-05 00:00:00.0
atimestamp millis=1301950800000
atimestamp GRK format=2011-04-05 00:00:00.0 +0300
atimestamp GMT format=2011-04-04 21:00:00.0 +0000
atimestamptz=2011-04-05 00:00:00.0
atimestamptz millis=1301950800000
atimestamptz GRK format=2011-04-05 00:00:00.0 +0300
atimestamptz GMT format=2011-04-04 21:00:00.0 +0000
atime=00:00:00
atime millis=-7200000
atime in hours=-2.0
atimetz=23:00:00
atimetz millis=-10800000
atimetz in hours=-3.0
IMO, the values it gets for the atimestamp (without tz) and atimestamptz (with tz) are sane.
I dont supply any Calendar in getTimestamp for the "without tz" timestamp so the driver correctly assumes
we are talking about the local Greek time zone. Correctly 2011-04-05 00:00:00.0 +0300=2011-04-04 21:00:00.0 +0000
However i dont quite get the results for atime (without tz) and atimetz (with tz):
For atime it correctly prints the time 00:00:00, but the millis should be -10800000, and in hours should be -3.0.
For atimetz it incorrectly prints "23:00:00" , but correctly states that the millis=-10800000, and in hours -3.0.
Shouldn't at least timetz print the same time as in the case with the timestamptz?
The below code prints:
st = con.prepareStatement("SELECT atimestamptz::timetz,atimestamptz FROM testts");
rs = st.executeQuery();
atimetz = rs.getTime(1);
out.println("atimetz="+atimetz+"<BR>");
atimestamptz = rs.getTimestamp(2);
out.println("atimestamptz="+atimestamptz+"<BR>");
atimetz=23:00:00
atimestamptz=2011-04-05 00:00:00.0
Thanx for any thoughts.
--
Achilleas Mantzios
On 6 April 2011 03:04, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote: > IMO, the values it gets for the atimestamp (without tz) and atimestamptz (with tz) are sane. > I dont supply any Calendar in getTimestamp for the "without tz" timestamp so the driver correctly assumes > we are talking about the local Greek time zone. Correctly 2011-04-05 00:00:00.0 +0300=2011-04-04 21:00:00.0 +0000 > However i dont quite get the results for atime (without tz) and atimetz (with tz): > For atime it correctly prints the time 00:00:00, but the millis should be -10800000, and in hours should be -3.0. > For atimetz it incorrectly prints "23:00:00" , but correctly states that the millis=-10800000, and in hours -3.0. > Shouldn't at least timetz print the same time as in the case with the timestamptz? What were the Greek timezone rules on Jan 1 1970? Because that's what it'll be applying (time objects are really just Date objects based around Jan 1 1970 - it doesn't work very well as you discovered, but blame JDBC for that one..) From your results I expect that it was +0200 on Jan 1 1970; therefore "00:00:00" == Jan 1, 1970, 00:00:00 locally == Dec 31, 1969, 22:00 GMT == -2 hours from the epoch (your 'atime' result) and "00:00:00+03" == Jan 1, 1970, 00:00:00 +0300 == Dec 31, 1969, 23:00 locally == Dec 31, 1969, 21:00 GMT == -3 hours from the epoch (your 'atimetz' result) Oliver
Στις Wednesday 06 April 2011 01:38:53 ο/η Oliver Jowett έγραψε: > On 6 April 2011 03:04, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote: > > > IMO, the values it gets for the atimestamp �(without tz) and atimestamptz (with tz) are sane. > > I dont supply any Calendar in getTimestamp for the "without tz" timestamp so the driver correctly assumes > > we are talking about the local Greek time zone. Correctly 2011-04-05 00:00:00.0 +0300=2011-04-04 21:00:00.0 +0000 > > However i dont quite get the results for atime (without tz) and atimetz (with tz): > > For atime it correctly prints the time 00:00:00, but the millis should be -10800000, and in hours should be -3.0. > > For atimetz it incorrectly prints "23:00:00" , but correctly states that the millis=-10800000, and in hours -3.0. > > Shouldn't at least timetz print the same time as in the case with the timestamptz? > > What were the Greek timezone rules on Jan 1 1970? > Because that's what it'll be applying (time objects are really just > Date objects based around Jan 1 1970 - it doesn't work very well as > you discovered, but blame JDBC for that one..) > > From your results I expect that it was +0200 on Jan 1 1970; > therefore "00:00:00" == Jan 1, 1970, 00:00:00 locally == Dec 31, 1969, > 22:00 GMT == -2 hours from the epoch (your 'atime' result) > and "00:00:00+03" == Jan 1, 1970, 00:00:00 +0300 == Dec 31, 1969, > 23:00 locally == Dec 31, 1969, 21:00 GMT == -3 hours from the epoch > (your 'atimetz' result) > > Oliver > It seems so Oliver, thanx. -- Achilleas Mantzios