Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] getting at the actual int4 value of an abstime

From: José Soares <jose(at)sferacarta(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu>, Jim Mercer <jim(at)reptiles(dot)org>, "Ross J(dot) Reedstrom" <reedstrm(at)wallace(dot)ece(dot)rice(dot)edu>, pgsql-general(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] getting at the actual int4 value of an abstime
Date: 1999-08-30 13:19:25
Message-ID: 37CA84DD.14C0C263@sferacarta.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
You don't need to create such function it works already on v6.5:

prova=> select date_part('epoch', current_date);
date_part
---------
935964000
(1 row)

prova=> select datetime(935964000);
datetime
---------------------------
30/08/1999 00:00:00.00 CEST
(1 row)

prova=> select date_part('epoch','30/08/1999 00:00:00.00 CEST'::datetime);
date_part
---------
935964000
(1 row)

José

Tom Lane ha scritto:

> Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu> writes:
> >>>> i want to get the results of a select as unix time_t, without having
> >>>> to use the expensive mktime()/strptime() unix C calls.
> >>>> is there a way to get the int4 value that postgres is storing raw
> >>>> for abstime?
>
> > postgres=> select date_part('epoch', timefield) from timetest;
> > date_part
> > ---------
> > 934957840
> > (1 rows)
>
> BTW, while rooting around in contrib/ I noticed that contrib/unixdate
> has an efficient way of going the other direction: just apply the
> conversion from abstime with a type cheat.  The coding is obsolete,
> but updated to 6.5, it works fine:
>
> regression=> CREATE FUNCTION datetime(int4) RETURNS datetime
> regression-> AS 'abstime_datetime' LANGUAGE 'internal';
> CREATE
> regression=> select datetime(935779244);
> datetime
> ----------------------------
> Fri Aug 27 14:40:44 1999 EDT
> (1 row)
> regression=> select date_part('epoch',
> regression-> 'Fri Aug 27 14:40:44 1999 EDT'::datetime);
> date_part
> ---------
> 935779244
> (1 row)
>
> Nifty.  I wonder whether we shouldn't move this contrib feature into the
> standard system for 6.6?  Perhaps with a less generic name, such as
> epoch2datetime() --- otherwise the parser will think that it can use the
> function as an automatic int4->datetime type conversion, which is probably
> Not a Good Idea.  But having both conversion directions would sure make
> life simpler and less error-prone for client apps that need to translate
> datetimes to and from time_t.
>
>                         regards, tom lane
>
> ************


In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 1999-08-30 14:33:32
Subject: Re: [HACKERS] An optimisation question
Previous:From: Horak DanielDate: 1999-08-30 12:15:09
Subject: IPC on win32 - additions for 6.5.2 and current trees

pgsql-general by date

Next:From: AlkeDate: 1999-08-30 13:56:23
Subject: Re: SERIAL vs. OIDs
Previous:From: José SoaresDate: 1999-08-30 12:18:52
Subject: Re: [GENERAL] OID vs SERIAL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group