Обсуждение: epoch to timestamp
can anyone tell me how to convert an epoch date to timestamp format?? TIA, -- Lucas Lain
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.
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
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) >
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
--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
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
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
--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
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
--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
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 :-) )
>
>
>
>