Re: How to use dateadd in databricks sql ?

alexa · ‎05-25-2022

I am trying to something like this but getting error like :

Error in SQL statement: AnalysisException: Undefined function: 'DATEADD'. This function is neither a registered temporary function nor a permanent function registered in the database 'default'.

DATEADD(HOUR,IFNULL(Hours_Adjusted,0),Date_Created) AS Date_Created_Local

I also tried, something like below but it seems interval doesn't support expression :

Date_Created+ INTERVAL Hours_Adjusted hours AS Date_Created_Local

Please help me !!

alexa · ‎05-25-2022

Beginning from Spark 3.0, you may use the

make_interval(years, months, weeks, days, hours, mins, secs)

function if you want to add intervals using values from other columns.

SELECT

id

, Start_time + make_interval(0, 0, 0, 0, time_zone_offset, 0, 0) AS Local_Start_Time

FROM MyTable

https://stackoverflow.com/questions/60209203/adding-variable-hours-to-timestamp-in-spark-sql

View solution in original post

SergeRielau · ‎05-27-2022

Dateadd was added in DBR 10.4 and is in DBSQL current.

SELECT DATEADD(HOUR,IFNULL(100, 0),current_date) AS Date_Created_Local

=> 2022-05-31T04:00:00.000+0000.

You can also use one of these casts to turn any wellformed string into an interval:

SELECT current_date + 'INTERVAL \'100\' HOUR'::INTERVAL HOUR;

SELECT current_date + CAST ('INTERVAL \'100\' HOUR' AS INTERVAL HOUR);