Thread: Conversion

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Lonh SENG
Sent: Tuesday, 13 August 2002 11:14 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Conversion

Hi all,

 

        How can I convert from int4 to date time?

 

 

Regards,

   

    Lonh

On Tue, Aug 13, 2002 at 11:32:25AM +0800, Christopher Kings-Lynne wrote:
> Is the int4 a UNIX epoch? ie. seconds since 1970?
> 
> If so, then this will generally work:
> 
> SELECT CAST(int4field AS abstime);
> 
> or
> 
> SELECT int4field::abstime;

http://www.postgresql.org/idocs/index.php?datatype-datetime.html says:
The types abstime  and reltime are lower precision types which are usedinternally. You are discouraged from using any
ofthese types in newapplications and are encouraged to move any old ones over whenappropriate. Any or all of these
internaltypes might disappear in afuture release.
 

Don't they?

-- 
Fduch M. Pravking

> On Tue, Aug 13, 2002 at 11:32:25AM +0800, Christopher Kings-Lynne wrote:
> > Is the int4 a UNIX epoch? ie. seconds since 1970?
> >
> > If so, then this will generally work:
> >
> > SELECT CAST(int4field AS abstime);
> >
> > or
> >
> > SELECT int4field::abstime;
>
> http://www.postgresql.org/idocs/index.php?datatype-datetime.html says:
>
>  The types abstime  and reltime are lower precision types which are used
>  internally. You are discouraged from using any of these types in new
>  applications and are encouraged to move any old ones over when
>  appropriate. Any or all of these internal types might disappear in a
>  future release.

Yes, but in absence of:

SELECT EXTRACT(TIMESTAMP FROM EPOCH '12341234234');

(Hint Hint Thomas!!!)

It's all he can do.  I suggest using the syntax above to convert his integer
column to a timestamp column.

Chris

On Tue, Aug 13, 2002 at 03:14:38PM +0800, Christopher Kings-Lynne wrote:
> > http://www.postgresql.org/idocs/index.php?datatype-datetime.html says:
> >
> >  The types abstime  and reltime are lower precision types which are used
> >  internally. You are discouraged from using any of these types in new
> >  applications and are encouraged to move any old ones over when
> >  appropriate. Any or all of these internal types might disappear in a
> >  future release.
> 
> Yes, but in absence of:
> 
> SELECT EXTRACT(TIMESTAMP FROM EPOCH '12341234234');

Sounds nice :)

> (Hint Hint Thomas!!!)
>
> It's all he can do.  I suggest using the syntax above to convert his integer
> column to a timestamp column.

Sure. I use the same. But I don't like it because of that caution :(

The other way is
SELECT 'epoch'::timestamp + (int4field::text || 's')::interval,
but it's much much slower... And it seems not to handle timestamps
after 2038-01-19.

-- 
Fduch M. Pravking