Timestamp with time zone

From: Xavier Robin <xavier(dot)robin(at)bluewin(dot)ch>
To: pgsql-novice(at)postgresql(dot)org
Subject: Timestamp with time zone
Date: 2010-06-30 12:59:37
Message-ID: 4C2B3FB9.20800@bluewin.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hello,

I'm running a Debian Lenny box with PostgreSQL 8.4.4 from the backports.
I have a question regarding timestamps with time zone. I can't get time
zones inserted into my timestamp with time zone columns. For example:

> CREATE TABLE testcase(
> created TIMESTAMP WITH TIME ZONE
> );
> INSERT INTO testcase VALUES (TIMESTAMP WITH TIME ZONE '2005-01-01 00:00+01');
> SELECT created, EXTRACT(TIMEZONE FROM created) FROM testcase;
> created | date_part
> ------------------------+-----------
> 2004-12-31 23:00:00+00 | 0
> (1 ligne)

Or from the docs:

> SELECT TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02';
> timestamptz
> ------------------------
> 2004-10-19 08:23:54+00

In both examples, the timezone is lost. I read that

> To ensure that a literal is treated as timestamp with time zone, give it the correct explicit type

but how can I do that in pactice?

Thanks,
Xavier

Browse pgsql-novice by date

  From Date Subject
Next Message Xavier Robin 2010-06-30 13:12:39 Timestamp with time zone
Previous Message Thom Brown 2010-06-30 12:41:45 Re: null vs empty string