From: | "Andrew B(dot) Young" <andrew_b_young(at)earthlink(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | How to store the time zone with a timestamp |
Date: | 2005-12-16 16:57:04 |
Message-ID: | 43A2F1E0.8060506@earthlink.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-12-16 17:12:08 | Re: is this a bug or I am blind? |
Previous Message | Eric E | 2005-12-16 16:55:21 | Re: Toolkit for creating editable grid |