PgSQL: Using "NOW() AT TIME ZONE 'UTC'" as default value

[Daniel Böhmer]

Hello everyone,

we are using a PgSQL database for our scientific database. We defined that all
timestamps shall be in UTC.

So we decided to use the data type "timestamp w/o time zone".

We didn't want to use and still do not want to migrate to timestamps w/ time
zone because they eat up 8 bytes which is the double of our current version.

Recently we discovered that all time values are off by 2h because we are
located in CEST which is UTC+02:00 at the moment.

PgSQL offers setting dates to "NOW() AT TIME ZONE 'UTC'". This saves the time
in the correct UTC time but without permanent time zone information.

I set one table's default value for the creation column to this value. In
plain SQL it works great. In RDBO I get this error message:

> Caught exception in MyApp::SomeModule->some_sub "insert() - Invalid default
> timestamp: 'timezone('UTC'::text, now())' at
> /home/boehmer/perl5/lib/perl5/Rose/DB/Object/Metadata.pm line 3810

They default value printed out is what the Rose::DB::Loader extracted from the
DB.

How to make RDBO work with such columns or how to achieve correct timestamps
otherwise?

Kind regards
Daniel Böhmer

--
Diese E-Mail ist mit GnuPG digital signiert. Bitte ignorieren Sie die
Datei "signature.asc".
This e-mail was digitally signed by GnuPG. Please ignore the file
"signature.asc".

Dipl.-Inf. (BA) Daniel Böhmer Leibniz Institute for
Mail: boe...@tropos.de Tropospheric Research
Phone: +49 341 235-2603 Permoser Str. 15
04318 Leipzig
Web: http://www.tropos.de/ GERMANY

[John Siracusa]

On Tue, Jun 14, 2011 at 12:15 PM, Daniel Böhmer <boe...@tropos.de> wrote:
> PgSQL offers setting dates to "NOW() AT TIME ZONE 'UTC'". This saves the time
> in the correct UTC time but without permanent time zone information.
>
> I set one table's default value for the creation column to this value. In
> plain SQL it works great. In RDBO I get this error message:
>
>> Caught exception in MyApp::SomeModule->some_sub "insert() - Invalid default
>> timestamp: 'timezone('UTC'::text, now())' at
>> /home/boehmer/perl5/lib/perl5/Rose/DB/Object/Metadata.pm line 3810
>
> They default value printed out is what the Rose::DB::Loader extracted from the
> DB.
>
> How to make RDBO work with such columns or how to achieve correct timestamps
> otherwise?

I'll have to modify RDBO to understand and allow that kind of default
value in Pg. I'm not sure when I'll get to it, but anyone is free to
give it a try and send me a patch :)

-John