Re: How to store the time zone with a timestamp

BTW, if you wanted a more integrated solution, you could build a custom
type that would store the timestamp info. There's a good chance such
code would end up being what's used in the server as the official type,
too.

On Fri, Dec 16, 2005 at 08:57:04AM -0800, Andrew B. Young wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461