Search Postgresql Archives

How to store the time zone with a timestamp

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux