CODEWITHSUNDEEP
sqlpostgresql
Fakhryan Albar
Fakhryan Albar

Reputation: 113

PGSQL convert time to second

why i get error with this code

SELECT EXTRACT(SECOND FROM TIME total_time) from tr_empl_quiz;

and i got error to with this code 

SELECT EXTRACT(EPOCH FROM TIMESTAMP total_time) from tr_empl_quiz;

this is my table content tr_empl_quiz and type data is time without time zone

total_time
============
00:01:00

00:02:00

enter image description here

enter image description here

Upvotes: 1

Views: 1894

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

When you use the extract() function you are getting the value of the date/time part. In your examples, the seconds are zero, so that is what you get.

Postgres does support what you want, using the perhaps unintuitive name epoch. Epoch returns the number of seconds. For an date or datetime value, this is the number since 1970-01-01 (the beginning of Unix time). For a time or interval it is the total number of seconds during the period. So:

select extract(epoch from time '00:02:00')

returns 120.

Surprisingly, the documentation for epoch doesn't mention that it works on the time data type. The functionality is entirely consistent with what the function does. Either the documentation (which is generally quite excellent) overlooks time; or time is treated as an interval.

For a column in a table, you would just do:

SELECT EXTRACT(EPOCH FROM total_time) 
FROM tr_empl_quiz;

or:

SELECT EXTRACT(EPOCH FROM CAST(total_time as time)) 
FROM tr_empl_quiz;

Depending on what you want.

Upvotes: 2

Related Questions