Thread: date calculation

Hi there,

I have a problem calculating a date. A field carries the date as passed
seconds since Jan 1st 1970.
How can I get the date as dd.mm.yyyy out of this??

Thanks a lot 
Michael

> Hi there,
> 
> I have a problem calculating a date. A field carries the date as passed
> seconds since Jan 1st 1970.
> How can I get the date as dd.mm.yyyy out of this??

cast('1970-1-1' as timestamp)+cast(your_ticks || ' seconds' as interval)

Does anyone know better way to cast it?

Regards,
Tomasz Myrta

Tomasz Myrta <jasiek@klaster.net> writes:
> cast('1970-1-1' as timestamp)+cast(your_ticks || ' seconds' as interval)

> Does anyone know better way to cast it?

The above will probably give the wrong answer (off by your timezone
offset).  The reference point should be zero hour GMT, but the first
cast will give zero hour local time.  You should cast the reference
to timestamp with time zone (timestamptz) instead.

Also, I'd suggest using float-times-interval as a simpler and more
efficient way of forming the offset interval.  So:

select 'epoch'::timestamp with time zone + your_ticks * '1 second'::interval;
        regards, tom lane