ORA-00936 When using date function in the oracle select statement

Question

I have a query that I'm trying to aggregate data based on hours between two unix timestamps in Oracle. Hardest part is that I get error'd out with "ORA-00936: missing expression" error even I don't see anything wrong in the query. Need some expert advice here. Below is the query -

Query -

select DATE(FROM_UNIXTIME(C.DATETIMEORIGINATION)) the_date,
HOUR(FROM_UNIXTIME(C.DATETIMEORIGINATION)) the_hour,
count(c.RECORD_ID) the_count 
FROM TABLE_A C 
WHERE C.DATETIMEORIGINATION between 1380033019 AND 1379702408 
GROUP BY 1,2;

Any help is greatly appreciated. Thanks

Sevenup

DATE(), HOUR() and FROM_UNIXTIME() are not built-in Oracle functions. You can't always use code from another RDBMS directly. Though I guess you could have created them as functions yourself. What output do you want - that will determine the most appropriate equivalent? You also can't use positional notation in the group by clause. – Anus

This is intresting. I checked the Oracle site (docs.oracle.com/cd/E17952_01/refman-5.1-en/…) and it has functions that am using and their implementation. Not sure if am missing something here. – Sevenup

That's the MySQL documentation, not Oracle Database. Which isn't entirely obvious from that page, apart from a few references, mostly in command prompts. The home link at the bottom of the page makes it a bit clearer. You're far from the the first to be confused by both being under docs.oracle.com... – Anus

ok.. You got me with my pants down. :P I'm trying to count records based on hours between two unix timestamps. Appreciate your help.! – Sevenup

Answer 1

If you want the the_date field as an actual date:

select trunc(date '1970-01-01' + datetimeorigination / (24*60*60)) as the_date,
  to_char(date '1970-01-01' + datetimeorigination / (24*60*60),
    'HH24') as the_hour,
  count(record_id)
from table_a
group by trunc(date '1970-01-01' + datetimeorigination / (24*60*60)),
  to_char(date '1970-01-01' + datetimeorigination / (24*60*60), 'HH24');

THE_DATE  THE_HOUR COUNT(RECORD_ID)
--------- -------- ----------------
24-SEP-13 14                      1 
20-SEP-13 18                      1 

If you want the hour value as a number you can wrap that field in a to_number() call. If this is for display then you should explicitly format the date as well:

select to_char(date '1970-01-01' + datetimeorigination / (24*60*60),
    'YYYY-MM-DD') as the_date,
  to_char(date '1970-01-01' + datetimeorigination / (24*60*60),
    'HH24') as the_hour,
  count(record_id)
from table_a
group by to_char(date '1970-01-01' + datetimeorigination / (24*60*60),
    'YYYY-MM-DD'),
  to_char(date '1970-01-01' + datetimeorigination / (24*60*60), 'HH24');

THE_DATE   THE_HOUR COUNT(RECORD_ID)
---------- -------- ----------------
2013-09-24 14                      1 
2013-09-20 18                      1 

Or with one field for the date and time together:

select to_char(date '1970-01-01' + datetimeorigination / (24*60*60),
    'YYYY-MM-DD HH24') as the_hour,
  count(record_id)
from table_a
group by to_char(date '1970-01-01' + datetimeorigination / (24*60*60),
    'YYYY-MM-DD HH24');

THE_HOUR      COUNT(RECORD_ID)
------------- ----------------
2013-09-24 14                1 
2013-09-20 18                1 

Depends what you want to see and what you're going to do with it.

Whichever fields you use for the aggregation, you need to specify them the same way in the group by clause - you can't use positional notation, e.g. group by 1, 2. ANd you already realised that the between values have to be in ascending order or it won't find anything at all.

Anus

Excellent. Works great and this is what am expecting. Thank you so much and you saved my day @Alex Poole. I greatly appreciate your help here. – Sevenup