pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedepoch to timestamp
12+ messages / 7 participants
[nested] [flat]
* epoch to timestamp
@ 2003-05-09 21:05 Lucas Lain <lainl@aconectarse.com>
0 siblings, 1 reply; 12+ messages in thread
From: Lucas Lain @ 2003-05-09 21:05 UTC (permalink / raw)
To: pgsql-sql
can anyone tell me how to convert an epoch date to timestamp format??
TIA,
--
Lucas Lain
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: epoch to timestamp
@ 2003-05-09 21:36 Bruno Wolff III <bruno@wolff.to>
parent: Lucas Lain <lainl@aconectarse.com>
0 siblings, 1 reply; 12+ messages in thread
From: Bruno Wolff III @ 2003-05-09 21:36 UTC (permalink / raw)
To: Lucas Lain <lainl@aconectarse.com>; +Cc: pgsql-sql
On Fri, May 09, 2003 at 18:05:45 -0300,
Lucas Lain <lainl@aconectarse.com> wrote:
>
> can anyone tell me how to convert an epoch date to timestamp format??
If the epoch date is an integer with seconds since the unix epoch,
you can cast it to abstime and then to timestamp.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: epoch to timestamp
@ 2003-05-12 15:05 Lucas Lain <lainl@aconectarse.com>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 12+ messages in thread
From: Lucas Lain @ 2003-05-12 15:05 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
i really dont know how to do it ... i'm a newbie
it is something like this?
select cast(105471234 AS abstime) ...
and then?
On Fri, 9 May 2003 16:36:48 -0500
Bruno Wolff III <bruno@wolff.to> wrote:
> On Fri, May 09, 2003 at 18:05:45 -0300,
> Lucas Lain <lainl@aconectarse.com> wrote:
> >
> > can anyone tell me how to convert an epoch date to timestamp format??
>
> If the epoch date is an integer with seconds since the unix epoch,
> you can cast it to abstime and then to timestamp.
>
--
Lucas Lain
lainl@aconectarse.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: epoch to timestamp
@ 2003-05-12 15:22 Randall Lucas <rlucas@tercent.net>
parent: Lucas Lain <lainl@aconectarse.com>
1 sibling, 0 replies; 12+ messages in thread
From: Randall Lucas @ 2003-05-12 15:22 UTC (permalink / raw)
To: Lucas Lain <lainl@aconectarse.com>; +Cc: pgsql-sql
Hi Lucas,
Just nest or chain your casts as appropriate.
example=> select cast(cast(105471234 as abstime) as timestamp);
timestamp
---------------------
1973-05-05 13:33:54
(1 row)
Time: 1.81 ms
example=> select 105471234::abstime::timestamp;
timestamp
---------------------
1973-05-05 13:33:54
(1 row)
Time: 1.97 ms
Best,
Randall
On Monday, May 12, 2003, at 11:05 AM, Lucas Lain wrote:
> i really dont know how to do it ... i'm a newbie
> it is something like this?
>
> select cast(105471234 AS abstime) ...
>
> and then?
>
>
>
> On Fri, 9 May 2003 16:36:48 -0500
> Bruno Wolff III <bruno@wolff.to> wrote:
>
>> On Fri, May 09, 2003 at 18:05:45 -0300,
>> Lucas Lain <lainl@aconectarse.com> wrote:
>>>
>>> can anyone tell me how to convert an epoch date to timestamp format??
>>
>> If the epoch date is an integer with seconds since the unix epoch,
>> you can cast it to abstime and then to timestamp.
>>
>
>
> --
> Lucas Lain
> lainl@aconectarse.com
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: epoch to timestamp
@ 2003-05-12 15:26 Larry Rosenman <ler@lerctr.org>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 1 reply; 12+ messages in thread
From: Larry Rosenman @ 2003-05-12 15:26 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; Lucas Lain <lainl@aconectarse.com>; +Cc: Bruno Wolff III <bruno@wolff.to>; pgsql-sql
--On Monday, May 12, 2003 18:30:11 -0200 Achilleus Mantzios
<achill@matrix.gatewaynet.com> wrote:
> On Mon, 12 May 2003, Lucas Lain wrote:
>
>> i really dont know how to do it ... i'm a newbie
>> it is something like this?
>>
>> select cast(105471234 AS abstime) ...
>>
>> and then?
>
> Just do
> select 105471234::abstime::timestamp;
>
Along the same lines, how can I get from seconds to hour/minute/seconds?
(an interval?)
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: epoch to timestamp
@ 2003-05-12 15:27 Lucas Lain <lainl@aconectarse.com>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 0 replies; 12+ messages in thread
From: Lucas Lain @ 2003-05-12 15:27 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: bruno@wolff.to; pgsql-sql
thank everybody ... thank you very much!
works great!
On Mon, 12 May 2003 18:30:11 -0200 (GMT+2)
Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> On Mon, 12 May 2003, Lucas Lain wrote:
>
> > i really dont know how to do it ... i'm a newbie
> > it is something like this?
> >
> > select cast(105471234 AS abstime) ...
> >
> > and then?
>
> Just do
> select 105471234::abstime::timestamp;
>
> >
> >
> >
> > On Fri, 9 May 2003 16:36:48 -0500
> > Bruno Wolff III <bruno@wolff.to> wrote:
> >
> > > On Fri, May 09, 2003 at 18:05:45 -0300,
> > > Lucas Lain <lainl@aconectarse.com> wrote:
> > > >
> > > > can anyone tell me how to convert an epoch date to timestamp format??
> > >
> > > If the epoch date is an integer with seconds since the unix epoch,
> > > you can cast it to abstime and then to timestamp.
> > >
> >
> >
> >
>
> --
> ==================================================================
> Achilleus Mantzios
> S/W Engineer
> IT dept
> Dynacom Tankers Mngmt
> Nikis 4, Glyfada
> Athens 16610
> Greece
> tel: +30-210-8981112
> fax: +30-210-8981877
> email: achill@matrix.gatewaynet.com
> mantzios@softlab.ece.ntua.gr
>
>
--
Lucas Lain
lainl@aconectarse.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: epoch to timestamp
@ 2003-05-12 15:35 Larry Rosenman <ler@lerctr.org>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 12+ messages in thread
From: Larry Rosenman @ 2003-05-12 15:35 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
--On Monday, May 12, 2003 18:39:59 -0200 Achilleus Mantzios
<achill@matrix.gatewaynet.com> wrote:
> On Mon, 12 May 2003, Larry Rosenman wrote:
>
> As an example see:
>
> select date_part('minute',(105471234::abstime::timestamp) + ('10
> years')::interval);
>
I actually have just seconds (from my LD carrier), and want to store it in
hours/minutes/seconds.
>> >
>> Along the same lines, how can I get from seconds to hour/minute/seconds?
>>
>> (an interval?)
>>
>>
>>
>
> --
> ==================================================================
> Achilleus Mantzios
> S/W Engineer
> IT dept
> Dynacom Tankers Mngmt
> Nikis 4, Glyfada
> Athens 16610
> Greece
> tel: +30-210-8981112
> fax: +30-210-8981877
> email: achill@matrix.gatewaynet.com
> mantzios@softlab.ece.ntua.gr
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: epoch to timestamp
@ 2003-05-12 16:00 Josh Berkus <josh@agliodbs.com>
parent: Larry Rosenman <ler@lerctr.org>
0 siblings, 1 reply; 12+ messages in thread
From: Josh Berkus @ 2003-05-12 16:00 UTC (permalink / raw)
To: Larry Rosenman <ler@lerctr.org>; Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
Larry,
> I actually have just seconds (from my LD carrier), and want to store it in
> hours/minutes/seconds.
If you store it as an interval, you will end up with:
staffos=# select '12742329 seconds'::INTERVAL;
interval
-------------------
147 days 11:32:09
In fact, you can't avoid interval conversion to days, hours, minutes.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: epoch to timestamp
@ 2003-05-12 16:02 Larry Rosenman <ler@lerctr.org>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 12+ messages in thread
From: Larry Rosenman @ 2003-05-12 16:02 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
--On Monday, May 12, 2003 09:00:11 -0700 Josh Berkus <josh@agliodbs.com>
wrote:
> Larry,
>
>> I actually have just seconds (from my LD carrier), and want to store it
>> in hours/minutes/seconds.
>
> If you store it as an interval, you will end up with:
>
> staffos=# select '12742329 seconds'::INTERVAL;
> interval
> -------------------
> 147 days 11:32:09
>
> In fact, you can't avoid interval conversion to days, hours, minutes.
Yeah, I remembered that after I hit send (so, what else is new? /me
looking like
a dummy :-) )
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: epoch to timestamp
@ 2003-05-12 17:13 Chris Linstruth <cjl@QNET.COM>
parent: Larry Rosenman <ler@lerctr.org>
0 siblings, 0 replies; 12+ messages in thread
From: Chris Linstruth @ 2003-05-12 17:13 UTC (permalink / raw)
To: pgsql-sql
I wanted an interval to be displayed as HH:MI:SS even when the
number of hours is greater than 24. I resorted to something like
this:
acctsessiontime is an interval.
SELECT
date_part('seconds', acctsessiontime) as connectseconds,
date_part('minutes, acctsessiontime) as connectminutes,
date_part('hours', acctsessiontime) as connecthours,
date_part('days', acctsessiontime) as connectdays
....
I then did the old connecthours += connectdays * 24 routine.
Is there some sort of inverse "date_trunc" that would enable me
to say: to_char(acctsessiontime, 'HH:MI:SS') and get, for example,
147:23:12?
--
Chris Linstruth <cjl@qnet.com>
QNET
1529 East Palmdale Blvd Suite 200
Palmdale, CA 93550
(661) 538-2028
On Mon, 12 May 2003, Larry Rosenman wrote:
>
>
> --On Monday, May 12, 2003 09:00:11 -0700 Josh Berkus <josh@agliodbs.com>
> wrote:
>
> > Larry,
> >
> >> I actually have just seconds (from my LD carrier), and want to store it
> >> in hours/minutes/seconds.
> >
> > If you store it as an interval, you will end up with:
> >
> > staffos=# select '12742329 seconds'::INTERVAL;
> > interval
> > -------------------
> > 147 days 11:32:09
> >
> > In fact, you can't avoid interval conversion to days, hours, minutes.
> Yeah, I remembered that after I hit send (so, what else is new? /me
> looking like
> a dummy :-) )
>
>
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: epoch to timestamp
@ 2003-05-12 20:30 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Lucas Lain <lainl@aconectarse.com>
1 sibling, 2 replies; 12+ messages in thread
From: Achilleus Mantzios @ 2003-05-12 20:30 UTC (permalink / raw)
To: Lucas Lain <lainl@aconectarse.com>; +Cc: Bruno Wolff III <bruno@wolff.to>; pgsql-sql
On Mon, 12 May 2003, Lucas Lain wrote:
> i really dont know how to do it ... i'm a newbie
> it is something like this?
>
> select cast(105471234 AS abstime) ...
>
> and then?
Just do
select 105471234::abstime::timestamp;
>
>
>
> On Fri, 9 May 2003 16:36:48 -0500
> Bruno Wolff III <bruno@wolff.to> wrote:
>
> > On Fri, May 09, 2003 at 18:05:45 -0300,
> > Lucas Lain <lainl@aconectarse.com> wrote:
> > >
> > > can anyone tell me how to convert an epoch date to timestamp format??
> >
> > If the epoch date is an integer with seconds since the unix epoch,
> > you can cast it to abstime and then to timestamp.
> >
>
>
>
--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: epoch to timestamp
@ 2003-05-12 20:39 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Larry Rosenman <ler@lerctr.org>
0 siblings, 1 reply; 12+ messages in thread
From: Achilleus Mantzios @ 2003-05-12 20:39 UTC (permalink / raw)
To: Larry Rosenman <ler@lerctr.org>; +Cc: pgsql-sql
On Mon, 12 May 2003, Larry Rosenman wrote:
As an example see:
select date_part('minute',(105471234::abstime::timestamp) + ('10
years')::interval);
> >
> Along the same lines, how can I get from seconds to hour/minute/seconds?
>
> (an interval?)
>
>
>
--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2003-05-12 20:39 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-09 21:05 epoch to timestamp Lucas Lain <lainl@aconectarse.com>
2003-05-09 21:36 ` Bruno Wolff III <bruno@wolff.to>
2003-05-12 15:05 ` Lucas Lain <lainl@aconectarse.com>
2003-05-12 15:22 ` Randall Lucas <rlucas@tercent.net>
2003-05-12 20:30 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-05-12 15:26 ` Larry Rosenman <ler@lerctr.org>
2003-05-12 20:39 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-05-12 15:35 ` Larry Rosenman <ler@lerctr.org>
2003-05-12 16:00 ` Josh Berkus <josh@agliodbs.com>
2003-05-12 16:02 ` Larry Rosenman <ler@lerctr.org>
2003-05-12 17:13 ` Chris Linstruth <cjl@QNET.COM>
2003-05-12 15:27 ` Lucas Lain <lainl@aconectarse.com>
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