Skip site navigation (1) Skip section navigation (2)

Re: Timestamp with time zone

From: Tim Landscheidt <tim(at)tim-landscheidt(dot)de>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Timestamp with time zone
Date: 2010-06-30 16:59:53
Message-ID: m34ogkcv7a.fsf@passepartout.tim-landscheidt.de (view raw or flat)
Thread:
Lists: pgsql-novice
Xavier Robin <xavier(dot)robin(at)bluewin(dot)ch> wrote:

>> So if you need the timezone information, you'll have to re-
>> cord it separately.

> Oh, I was thinking about that, but it sounded weird because it was what
> I was expecting from a TIMESTAMP WITH*OUT* TIME ZONE column.

> Just one more question to make sure I got it all right:

> EXTRACT(timezone FROM <a timestamptz here>)

> will *always* return 0? Because the doc suggests that positive or
> negative values can be returned…

No, it will return the offset of your current time zone:

| tim=# BEGIN WORK;
| BEGIN
| tim=# CREATE TEMPORARY TABLE TestTZ (t TIMESTAMP WITH TIME ZONE);
| CREATE TABLE
| tim=# INSERT INTO TestTZ (t) VALUES (now());
| INSERT 0 1
| tim=# SET LOCAL timezone TO ':Europe/Athens'; SELECT EXTRACT(timezone FROM t) FROM TestTZ;
| SET
|  date_part
| -----------
|      10800
| (1 Zeile)

| tim=# SET LOCAL timezone TO ':Europe/Berlin'; SELECT EXTRACT(timezone FROM t) FROM TestTZ;
| SET
|  date_part
| -----------
|       7200
| (1 Zeile)

| tim=# SET LOCAL timezone TO 'UTC'; SELECT EXTRACT(timezone FROM t) FROM TestTZ;
| SET
|  date_part
| -----------
|          0
| (1 Zeile)

| tim=#

> And (just 1 more) so what's the point of this field in the first place?
> [...]

I don't know :-).

Tim


In response to

Responses

pgsql-novice by date

Next:From: Michael GlaesemannDate: 2010-06-30 17:42:52
Subject: Re: Timestamp with time zone
Previous:From: Tom LaneDate: 2010-06-30 16:55:59
Subject: Re: Compiling under MSYS and Windows 7

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group