How do I get the current time in seconds in the unix epoch?
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
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
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
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
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