Xavier Robin <xavier.robin@bluewin.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