pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddate calculation
3+ messages / 3 participants
[nested] [flat]
* date calculation
@ 2003-08-22 06:25 MichaelHoeller@t-online.de
0 siblings, 1 reply; 3+ messages in thread
From: MichaelHoeller@t-online.de @ 2003-08-22 06:25 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: date calculation
@ 2003-08-22 06:42 Tomasz Myrta <jasiek@klaster.net>
parent: MichaelHoeller@t-online.de
0 siblings, 1 reply; 3+ messages in thread
From: Tomasz Myrta @ 2003-08-22 06:42 UTC (permalink / raw)
To: MichaelHoeller@t-online.de; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: date calculation
@ 2003-08-22 12:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2003-08-22 12:56 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: MichaelHoeller@t-online.de; pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-08-22 12:56 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-22 06:25 date calculation MichaelHoeller@t-online.de
2003-08-22 06:42 ` Tomasz Myrta <jasiek@klaster.net>
2003-08-22 12:56 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox