CODEWITHSUNDEEP
postgresqldatetime
Christopher Schultz
Christopher Schultz

Reputation: 20837

How to convert a timestamp into seconds (and fractions) in PostgreSQL

I'm trying to get some (application) performance data from a PostgreSQL database by looking at two dates in a record, subtracting them and getting the result as a number of seconds with fractions.

It seems I'm able to get a timestamp which includes hours, minutes, seconds, and milliseconds, or I can get just the seconds (and fractions) without e.g. the minutes or I can get a UNIX timestamp which is only seconds (without the milliseconds). Is there a convenient way to convert a timestamp to seconds+millis?

Example:

SELECT extract(SECOND FROM TIME '00:01:02.1234') as secs;
secs
--------
2.1234

I was hoping to get 62.1234 (that's 1 * 60 + 02.1234) as the return value for overall seconds, not just the "seconds component" from the time value.

Is there an existing function to do that, or do I have to EXTRACT each part, multiply, and add each component together?

Upvotes: 1

Views: 1822

Answers (1)

Adrian Klaver
Adrian Klaver

Reputation: 19570

Use EPOCH:

SELECT extract(EPOCH FROM TIME '00:01:02.1234') as secs; 62.1234

Upvotes: 1

Related Questions