Thread: Bug in timezone-parsing?

Hi!

I found a problem with the parsing of timezone-offsets in the current
PG-driver. It seems something weird happened between 1921 and 1922, as the
offset changes from hour-precision to second-precision:

postgres=> select '1921-01-01'::timestamptz, '1922-01-01'::timestamptz;
         timestamptz          |      timestamptz
------------------------------+------------------------
 1921-01-01 00:00:00+01:39:52 | 1922-01-01 00:00:00+02

Trying to read a value like this will throw an exception, although the older
drivers were able to parse it just fine.

Here's the full bugreport:

* Timezone: EEST (GMT+2, @DST GMT+3)

* JDBC driver build number:
  - works: pg74.216.jdbc3.jar
  - works: postgresql-8.0-318.jdbc3.jar
  - fails: postgresql-8.1-408.jdbc3.jar
  - fails: postgresql-8.2-504.jdbc3.jar
  - fails: CVS 07. April 2007 13:32

* Server version:
  - PostgreSQL 8.2.3 (vanilla from postgresql.org)

* Java version (Linux 2.6.8/x86):
  - fails: Sun 1.4.2_09-b05
  - fails: Sun 1.5.0_06-b05
  - fails: Sun 1.6.0-b105

* Exact error message and stacktrace:

  Exception in thread "main" org.postgresql.util.PSQLException: Bad value for type timestamp : 1921-01-01
00:00:00+01:39:52
    at org.postgresql.jdbc2.TimestampUtils.loadCalendar(TimestampUtils.java:232)
    at org.postgresql.jdbc2.TimestampUtils.toTimestamp(TimestampUtils.java:307)
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getTimestamp(AbstractJdbc2ResultSet.java:419)
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getTimestamp(AbstractJdbc2ResultSet.java:2352)
    at Offset.main(Offset.java:22)
  Caused by: java.lang.NumberFormatException: Trailing junk on timestamp: ''
    at org.postgresql.jdbc2.TimestampUtils.loadCalendar(TimestampUtils.java:226)
    ...

* What you were doing, ideally in code form:

import java.sql.*;

public final class Offset
{
    public final static void main(String argv[])
        throws Exception
    {
        // configure before running!
        String url = "jdbc:postgresql://localhost:5432/postgres";
        String user = "";
        String pass = "";
        String query = "select '1921-01-01'::timestamptz as ts";

        Class.forName("org.postgresql.Driver").newInstance();
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;

        try
        {
            connection = DriverManager.getConnection(url, user, pass);
            statement = connection.createStatement();
            rs = statement.executeQuery(query);

            while (rs.next())
                System.out.println(rs.getTimestamp(1));
        }
        finally
        {
            if (rs != null)
                rs.close();

            if (statement != null)
                statement.close();

            if (connection != null)
                connection.close();
        }
    }
};

-Ville

On Mon, 16 Apr 2007, villeja@avoltus.com wrote:

> I found a problem with the parsing of timezone-offsets in the current
> PG-driver. It seems something weird happened between 1921 and 1922, as the
> offset changes from hour-precision to second-precision:
>
> postgres=> select '1921-01-01'::timestamptz, '1922-01-01'::timestamptz;
>         timestamptz          |      timestamptz
> ------------------------------+------------------------
> 1921-01-01 00:00:00+01:39:52 | 1922-01-01 00:00:00+02
>
> Trying to read a value like this will throw an exception, although the older
> drivers were able to parse it just fine.

Timezone offsets with seconds are new in the 8.2 server and the driver was
never updated for them.  Older drivers don't have as much error detection
and silently ignore the second portion while newer drivers complain that
they're getting something they don't expect.

Will fix, thanks for the complete report.

Kris Jurka

villeja@avoltus.com writes:
> I found a problem with the parsing of timezone-offsets in the current
> PG-driver. It seems something weird happened between 1921 and 1922, as the
> offset changes from hour-precision to second-precision:

> postgres=> select '1921-01-01'::timestamptz, '1922-01-01'::timestamptz;
>          timestamptz          |      timestamptz
> ------------------------------+------------------------
>  1921-01-01 00:00:00+01:39:52 | 1922-01-01 00:00:00+02

FWIW, that is expected behavior if you're using the Europe/Helsinki time
zone data:

# Zone    NAME        GMTOFF    RULES    FORMAT    [UNTIL]
Zone    Europe/Helsinki    1:39:52 -    LMT    1878 May 31
            1:39:52    -    HMT    1921 May    # Helsinki Mean Time
            2:00    Finland    EE%sT    1981 Mar 29 2:00
            2:00    EU    EE%sT

Quite a lot of places were observing local mean time up to the early
1900s, so you'll see very strange GMT offsets for dates that far back.

            regards, tom lane

On Mon, 16 Apr 2007, Kris Jurka wrote:

> Timezone offsets with seconds are new in the 8.2 server and the driver was
> never updated for them.  Older drivers don't have as much error detection and
> silently ignore the second portion while newer drivers complain that they're
> getting something they don't expect.
>

Fix commited to cvs for 8.2 and head.

Kris Jurka

> >Timezone offsets with seconds are new in the 8.2 server and the driver was
> >never updated for them.  Older drivers don't have as much error detection
> >and silently ignore the second portion while newer drivers complain that
> >they're getting something they don't expect.
>
> Fix commited to cvs for 8.2 and head.

CVS-version now works perfectly, thank you for the very fast response!

The exact timezone I'm using is indeed Europe/Helsinki (as Tom Lane
suggested) which now seems to be included to the unit-test as well =).

-Ville