Обсуждение: Trigger to convert UNIX time to timestamp without time zone.

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