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@xxxxxxxxxxxxxx so that your > message can get through to the mailing list cleanly > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@xxxxxxxxxxxxx Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
