Basically I am trying to convert a date field from my database into a number of seconds old. I used UNIX_TIMESTAMP on our old MySQL database and am looking for an equivalent function in PostgreSQL.
Thanks in advance
Add a comment
|
3 Answers
SELECT extract(epoch FROM your_datetime_column)
FROM your_table
More details in the manual:
http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
Comments
This:
select CURRENT_TIMESTAMP - '1970-01-01';
Will give you an interval type. You can get seconds out of that interval result if you need it.
Comments
I am trying to convince existing MySQL queries to work, this is what I have, seem to be working fine to me.
CREATE OR REPLACE FUNCTION UNIX_TIMESTAMP(date_field timestamp with time zone) RETURNS integer
AS $$
BEGIN
RETURN extract(epoch FROM date_field);
END;
$$ LANGUAGE plpgsql;
Comments
