ORA-00936在Oracle select语句中使用date函数时 [英] ORA-00936 When using date function in the oracle select statement

问题描述

我有一个查询，我试图根据Oracle中两个unix时间戳之间的小时数来聚合数据.最难的是，即使在查询中没有发现任何错误，我也会收到错误消息" ORA-00936:缺少表达式".在这里需要一些专家建议.下面是查询-

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 -

查询-

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

推荐答案

如果要将the_date字段作为实际日期:

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 

如果要将小时值作为数字，则可以将该字段包装在to_number()调用中.如果这是为了显示，那么您还应该明确格式化日期:

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.

用于聚合的任何字段，都需要在group by子句中以相同的方式指定它们-您不能使用位置表示法，例如group by 1, 2.并且您已经意识到between值必须按升序排列，否则根本找不到任何内容.

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.

这篇关于ORA-00936在Oracle select语句中使用date函数时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！