CODEWITHSUNDEEP
sqloracle-databasetimestampextract
glc78
glc78

Reputation: 439

SQL extract hour from timestamp

This query: select EXTRACT(HOUR FROM 1435047532) as hour from TABLENAME;
Returns this error: "invalid extract field for extract source".
I'm trying to extract the hour from a given timestamp. Maybe the problem is the format of timestamp field is NUMBER and not TIMESTAMP?

Upvotes: 0

Views: 6100

Answers (2)

MT0
MT0

Reputation: 167982

You can convert your numeric seconds-since-epoch time to a TIMESTAMP type using:

TIMESTAMP '1970-01-01 00:00:00' + NUMTODSINTERVAL( your_time_since_epoch, 'SECOND' )

So to get the hours:

SELECT EXTRACT( HOUR FROM TIMESTAMP '1970-01-01 00:00:00'
                          + NUMTODSINTERVAL( 1435047532, 'SECOND' ) )
FROM   DUAL;

If you need to handle leap seconds then you will need to create a function/package to handle this.

Upvotes: 1

Piotr Rogowski
Piotr Rogowski

Reputation: 3880

try this

select extract(hour from (select to_date('19700101', 'YYYYMMDD') 
+ ( 1 / 24 / 60 / 60 ) * 1435047532 from dual)) from dual

Upvotes: 0

Related Questions