Postgres Pro
Downloads
Home   >   mailing lists

Thread: epoch to timestamp

epoch to timestamp

From
Lucas Lain
Date:
can anyone tell me how to convert an epoch date to timestamp format??

TIA,

--
Lucas Lain

Re: epoch to timestamp

From
Bruno Wolff III
Date:
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.

Re: epoch to timestamp

From
Lucas Lain
Date:
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

Re: epoch to timestamp

From
Randall Lucas
Date:
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)
>

Re: epoch to timestamp

From
Achilleus Mantzios
Date:
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

Re: epoch to timestamp

From
Larry Rosenman
Date:

--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

Re: epoch to timestamp

From
Lucas Lain
Date:
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

Re: epoch to timestamp

From
Achilleus Mantzios
Date:
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

Re: epoch to timestamp

From
Larry Rosenman
Date:

--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

Re: epoch to timestamp

From
Josh Berkus
Date:
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

Re: epoch to timestamp

From
Larry Rosenman
Date:

--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

Re: epoch to timestamp

From
Chris Linstruth
Date:
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 :-) )
>
>
>
>