Postgres Pro
Downloads
Home   >   mailing lists

Thread: How do I get the current time in seconds in the unix epoch?

How do I get the current time in seconds in the unix epoch?

From
Bill Studenmund
Date:
I have a function in PL/pgSQL which needs the current time in seconds
expressed as an int4. In 7.1 I was able to get this (I thought) with
date_part(''epoch'', timestamp ''now'') . That doesn't seem to work for me
in last week's -current.

Here's the PLpgSQL:
       v_seed := date_part(''epoch'', timestamp ''now'');

And here's the output:

NOTICE:  Error occurred while executing PL/pgSQL function
NOTICE:  line 4 at assignment
ERROR:  Timestamp with time zone units 'epoch' not recognized

What's the best way to do this?

Take care,

Bill

Re: How do I get the current time in seconds in the unix epoch?

From
Tom Lane
Date:
Bill Studenmund <wrstuden@netbsd.org> writes:
> In 7.1 I was able to get this (I thought) with
> date_part(''epoch'', timestamp ''now'') . That doesn't seem to work for me
> in last week's -current.

Indeed: in 7.1 I can do

test71=# select date_part('epoch', timestamp 'now');date_part
------------1002946239
(1 row)

but current sources give

regression=# select date_part('epoch', timestamp 'now');
ERROR:  Timestamp with time zone units 'epoch' not recognized

Thomas, I think you broke something.
        regards, tom lane

Re: How do I get the current time in seconds in the unix epoch?

From
"Christopher Kings-Lynne"
Date:
> I have a function in PL/pgSQL which needs the current time in seconds
> expressed as an int4. In 7.1 I was able to get this (I thought) with
> date_part(''epoch'', timestamp ''now'') . That doesn't seem to work for me
> in last week's -current.
>
> Here's the PLpgSQL:
>
>         v_seed := date_part(''epoch'', timestamp ''now'');
>
> And here's the output:
>
> NOTICE:  Error occurred while executing PL/pgSQL function
> NOTICE:  line 4 at assignment
> ERROR:  Timestamp with time zone units 'epoch' not recognized

Hmmm.  I don't know why date_part isn't working, but I now only use the
EXTRACT syntax for maximum SQL compatibility.  ie. Do this instead:

v_seed := EXTRACT (EPOCH FROM CURRENT_TIMESTAMP);

Cheers,

Chris

Re: How do I get the current time in seconds in the unix

From
Thomas Lockhart
Date:
> > In 7.1 I was able to get this (I thought) with
> > date_part(''epoch'', timestamp ''now'') . That doesn't seem to work for me
> > in last week's -current.
> Thomas, I think you broke something.

It was actually a side effect of changing the date/time parser to no
longer ignore unrecognized text fields. The previous behavior has been
there from the Beginning, and the new behavior meant that the search
routine no longer returns "ignore" as a status (which caused the calling
routine to drop into the "special case" tests including "epoch").

Anyway, I've got patches, so no worries...
                      - Thomas

Re: How do I get the current time in seconds in the unix

From
Bill Studenmund
Date:
On Mon, 15 Oct 2001, Christopher Kings-Lynne wrote:

> Hmmm.  I don't know why date_part isn't working, but I now only use the
> EXTRACT syntax for maximum SQL compatibility.  ie. Do this instead:
>
> v_seed := EXTRACT (EPOCH FROM CURRENT_TIMESTAMP);

Unfortunatly that gives the same error. I think the problem is that the
underlying code isn't liking the EPOCH timezone. Tom mentioned he had
patches.

Take care,

Bill