How to store the time zone with a timestamp

Поиск
Список
Период
Сортировка
От Andrew B. Young
Тема How to store the time zone with a timestamp
Дата
Msg-id 43A2F1E0.8060506@earthlink.net
обсуждение исходный текст
Ответы Re: How to store the time zone with a timestamp  ("Jim C. Nasby" <jnasby@pervasive.com>)
Список pgsql-general
I just realized that the type "timestamp with time zone" does NOT store
the time zone!
(And I just found this support list to look for help.)

I am developing an application that stores power (watts) readings from
meters
located around the world (abridged)--
    power_meter_id integer NOT NULL,
    "ts" timestamp with time zone NOT NULL,
    power_reading real NOT NULL

Not storing the originating TZ is unacceptable.  My search of this list
finds a number of
helpful discussions (including a reference to a TODO; soon I hope!)  I
don't know
database programming and wonder if experienced users could reply with
some code
they've implemented.

I think the best solution is along Karsten's, which I believe goes like
this--
1) "ts" timestamp with time zone NOT NULL,
     "tz" char( 6 ) NOT NULL,   # '-12:00' to '+13:00'
2) A insert trigger that populates "tz"
3)  Some function foo() that
    a) calls timezone( tz, ts ) and
    b) tacks on tz to the resulting string
  in a "SELECT power_meter_id, foo( ts, tz ), power_reading;"

ANY snip its of code that implements anything related would be appreciated.
I can probably gin the complete solution seeing enough examples.

Thanks!
Andrew

В списке pgsql-general по дате отправления:

Предыдущее
От: Eric E
Дата:
Сообщение: Re: Toolkit for creating editable grid
Следующее
От: Tom Lane
Дата:
Сообщение: Re: is this a bug or I am blind?