ruby-dbi-devel

[Ruby-DBI-devel] PostgreSQL data interpretation problems...

[Sean C. <se...@ch...>]

So after digging through trace output and after diving through as many
lines of SQL logs, I found what could be a nasty date parsing problem
that could be biting folks here as well and want to give everyone a
heads up.  The following code will demonstrate the problem:

# CREATE TABLE timestamp_test (
  utc_timestamp TIMESTAMP NOT NULL
);

# INSERT INTO timestamp_tmp VALUES ('2002-4-7 2:0:0.0');

# SELECT * from timestamp_tmp;
        utc_date       =20
------------------------
 2036-06-02 22:55:24-07
(1 row)

# INSERT INTO timestamp_tmp VALUES ('2002-4-7 -8 2:0:0.0');
# SELECT * from timestamp_tmp;
        utc_date       =20
------------------------
 2036-06-02 22:55:24-07
 2002-04-07 03:00:00-07
(2 rows)


The timestamp values are generated via dbi which is problematic
because there is no easy way of getting from CST or PST to -4 or -7.
Err.. this applies to PostgreSQL and only bit me as of
yesterday/today.  -sc

--=20
Sean Chittenden

Re: [Ruby-DBI-devel] PostgreSQL data interpretation problems...

[Sean C. <se...@ch...>]

> So after digging through trace output and after diving through as many
> lines of SQL logs, I found what could be a nasty date parsing problem
> that could be biting folks here as well and want to give everyone a
> heads up.  The following code will demonstrate the problem:
>=20
> # CREATE TABLE timestamp_test (
>   utc_timestamp TIMESTAMP NOT NULL
> );
>=20
> # INSERT INTO timestamp_tmp VALUES ('2002-4-7 2:0:0.0');

Daylight savings boundary conditions.  blah.  This problem happened
because mktime() failed on FreeBSD in this time area because 2am is
actually 3am and mktime() doesn't change the tm struct that gets passed
to it.  Hopefully this will be taken into account by postgres shortly
(patch already sent in).  Anyway, for those interested, data from
2002-Apr-7 2am-3am is probably being stored in the year 2036 if you're
using postgres and are on a system who's mktime() implementation
doesn't alter the tm struct.  Just a heads up/FYI: this bit me hard.
-sc

--=20
Sean Chittenden