Thread: Trigger to convert UNIX time to timestamp without time zone.
Hello,
I want to insert data into a column "timestamp without time zone" data type. The problem is I am receiving the data in UNIX time.
How can I create a trigger to transform the time from UNIX to timestamp without time zone every time a new record is inserted into my database?
Thank you in advance.
Regards,
Alberto.
Alberto Olivares Colas
Technical Consultant
Technical Consultant
Snowflake Software
Tel.: +44 (0)2380 386578
Registered in England & Wales. Registered Number: 4294244
-----------------------------------------------------------------------------------------
Geospatial Technology Company of the Year
Geospatial Technology Company of the Year
On 06/06/2014 06:19 AM, Alberto Olivares wrote: > Hello, > > I want to insert data into a column "timestamp without time zone" data > type. The problem is I am receiving the data in UNIX time. > > How can I create a trigger to transform the time from UNIX to timestamp > without time zone every time a new record is inserted into my database? to_timesstamp will take the epoch, which I assume is what you are talking about, and turn it into a timestamp with time zone. Don't worry about the timezone. test=> select to_timestamp(extract(epoch from now())); to_timestamp ------------------------------- 2014-06-06 06:27:20.484509-07 (1 row) test=> \d timestamp_test Table "public.timestamp_test" Column | Type | Modifiers --------+-----------------------------+----------- id | integer | ts | timestamp without time zone | ts_z | timestamp with time zone | Inserting a timestamp with time zone into a field that is timestamp without timezone will strip the timezone automatically. test=> insert into timestamp_test values (1, to_timestamp(extract(epoch from now())), to_timestamp(extract(epoch from now()))); INSERT 0 1 test=> select * from timestamp_test ; id | ts | ts_z ----+----------------------------+------------------------------- 1 | 2014-06-06 06:30:58.080158 | 2014-06-06 06:30:58.080158-07 (1 row) So you just need to wrap the above in a function and call it from your trigger. > > Thank you in advance. > > Regards, > Alberto. > > > *Alberto Olivares Colas > *Technical Consultant > Snowflake Software -- Adrian Klaver adrian.klaver@aklaver.com
On Fri, Jun 06, 2014 at 02:19:50PM +0100, Alberto Olivares wrote: > How can I create a trigger to transform the time from UNIX to timestamp > without time zone every time a new record is inserted into my database? This is in the manual, section 9.8: to_timestamp(double precision) It's always a little confusing when you go to look it up, because it's not with the date and time functions, because it's actually a formatting issue. (There's a cross reference, but if you don't know this is just a formatting issue you won't know to follow the reference.) You probably don't need a trigger, just put that in your query. Are you sure you want this without time zone? In my experience, almost every time people think they want "without time zone" they actually don't. A -- Andrew Sullivan ajs@crankycanuck.ca
Andrew Sullivan <ajs@crankycanuck.ca> wrote: > Are you sure you want this without time zone? In my experience, > almost every time people think they want "without time zone" they > actually don't. +1 Basically, if you want to capture a moment in time, such as when some event happened or some measurement was taken, you want timestamp WITH time zone. If you want to know what was (or will be) showing on a clock in some particular time zone at a moment in time, you want timestamp WITHOUT time zone. The names tend to mislead one on the semantics of these types. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company