0

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?

glc78
  • 439
  • 1
  • 8
  • 20
  • 3
    1435047532 is just a number, you need to define how it is interpreted as a date/time. Eg. is it seconds since the Unix epoch or 10ns intervals since 1600-01-01T00:00:00? – Richard Apr 05 '17 at 08:57
  • @Richard I edited question specifying the format is NUMBER amd asking if that is the problem. So you're right, it's.That numebr is seconds from Uniix epoch. How to convert them to the right format? – glc78 Apr 05 '17 at 09:03
  • Why do you even store such a unix number when you really want a timestamp? I suggest you change your table design and switch to `date` (which is a datetime really) or `timestamp` (i.e. create a new column, calculate ist values and delete the old column). – Thorsten Kettner Apr 05 '17 at 09:10
  • @ThorstenKettner I can't do it. I don't have permission to change anything on the structure, I can olny query. – glc78 Apr 06 '17 at 11:43
  • Too bad when someone makes a design error and others suffer from this. You should at least make this known so they are aware of it and may consider changing this. – Thorsten Kettner Apr 06 '17 at 11:47
  • @ThorstenKettner This database is going to be dismissed. I'm working on transfering all data they need into a build new time-series database. – glc78 Apr 06 '17 at 11:54

2 Answers2

1

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.

Community
  • 1
  • 1
MT0
  • 143,790
  • 11
  • 59
  • 117
  • 1
    We had this discussion already. Unix timestamp does not support leap seconds. However, you may consider that `1970-01-01 00:00:00` is always given as UTC time, not local. This is not a criticism to your answer but a hint to adapt it according to specific needs. – Wernfried Domscheit Apr 05 '17 at 09:43
  • 1
    @WernfriedDomscheit If the timestamp is needed in a particular time zone (i.e. PST) then it can be changed using `FROM_TZ( TIMESTAMP '1970-01-01 00:00:00' + NUMTODSINTERVAL( your_time_since_epoch, 'SECOND' ), 'UTC' ) AT TIME ZONE 'PST'`. – MT0 Apr 05 '17 at 10:50
0

try this

select extract(hour from (select to_date('19700101', 'YYYYMMDD') 
+ ( 1 / 24 / 60 / 60 ) * 1435047532 from dual)) from dual
Piotr Rogowski
  • 3,642
  • 19
  • 24
  • 1
    You get `ORA-30076: invalid field for extract source`. You need to use a `TIMESTAMP` if you want to extract hours. – MT0 Apr 05 '17 at 09:23