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
|
Список | 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 по дате отправления: