cdessanti
commented
2 years ago Hi,
you should explicitly define the data type of the date/timestamp in the dateadd function
select DATEADD('DAY', -30, time)
from jrw_onu_intftraffic_day_03
where onuid = '1661123795'
and itime >=DATEADD('DAY', -30,DATE '2022-03-01);if you prefer you can also use INTERVAL instead DATEADD/DATEDIFF, but you have to use the DATE/TIMESTAMP keyword depending if you are using a timestamp or a date.
DIfferently from Oralce in Heavydb the DATE datatype hasn't a TIME part.
Regards, Candido
Grammatical reference for date addition and subtraction: https://docs.heavy.ai/sql/data-manipulation-dml/functions-operators#statistical-and-aggregate-functions but just look like this the DATEADD cannot be put in a where condition?
heavysql> select DATEADD('DAY', -30, itime) from jrw_onu_intftraffic_day_03 where onuid = '1661123795' and itime >=DATEADD('DAY', -30,'2022-03-01 00:00:00'); SQL Error: From line 3, column 45 to line 3, column 88: Cannot apply 'DATEADD' to arguments of type 'DATEADD(<CHAR(3)>,, <CHAR(19)>)'. Supported form(s): 'DATEADD(, , )'
heavysql> select itime, receflux from jrw_onu_intftraffic_day_03 where onuid = '1661123795' and DATEDIFF('DAY', '2022-05-17 10:42:30',itime) >= 10; SQL Error: From line 3, column 34 to line 3, column 82: Cannot apply 'DATEDIFF' to arguments of type 'DATEDIFF(<CHAR(3)>, <CHAR(19)>,)'. Supported form(s): 'DATEDIFF(, , <
heavysql> select DATEADD('DAY', -30, itime) from jrw_onu_intftraffic_day_03 where onuid = '1661123795'; EXPR$0 2022-01-30 00:00:00 2022-01-31 00:00:00 2022-02-02 00:00:00 2022-02-04 00:00:00 2022-02-06 00:00:00 2022-02-09 00:00:00 2022-02-07 00:00:00 2022-02-11 00:00:00 2022-02-12 00:00:00 2022-02-13 00:00:00 2022-02-15 00:00:00 2022-02-18 00:00:00 2022-02-19 00:00:00 2022-02-22 00:00:00 2022-02-24 00:00:00 2022-02-23 00:00:00 2022-02-26 00:00:00 2022-03-01 00:00:00 2022-02-01 00:00:00 2022-02-03 00:00:00 2022-02-05 00:00:00 2022-02-10 00:00:00 2022-02-14 00:00:00 2022-02-20 00:00:00 2022-02-21 00:00:00 2022-02-25 00:00:00 2022-02-27 00:00:00 2022-02-28 00:00:00 28 rows returned. Execution time: 54 ms, Total time: 55 ms
The problems encountered are as follows: how to rewrite Oracle database-like syntax:
select count(*) from jrw_onu_intftraffic_day_03 where onuid = '1661123795' and itime between to_date('2022-05-17 10:42:30', 'YYYY-MM-DD HH24:MI:SS')-30 and to_date('2022-05-17 10:42:30', 'YYYY-MM-DD HH24:MI:SS');I know I can get rid of to_date, but how to subtract the conditional way of writing 30 days? I want to use dateadd or datediff as a condition, but I don't seem to support it. What do you need to do? thanks!