time and timetz : Do I miss something?

Поиск
Список
Период
Сортировка
От Achilleas Mantzios
Тема time and timetz : Do I miss something?
Дата
Msg-id 201104051804.18535.achill@matrix.gatewaynet.com
обсуждение исходный текст
Ответы Re: time and timetz : Do I miss something?  (Oliver Jowett <oliver@opencloud.com>)
Список pgsql-jdbc
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

В списке pgsql-jdbc по дате отправления:

Предыдущее
От: Daron Ryan
Дата:
Сообщение: Re: PreparedStatement for set membership (The IN operator)
Следующее
От: Oliver Jowett
Дата:
Сообщение: Re: time and timetz : Do I miss something?