Re: Timestamp with time zone

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