cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
cancel
Showing results for 
Search instead for 
Did you mean: 

How to use dateadd in databricks sql ?

alexa
New Contributor III

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 !!

1 ACCEPTED SOLUTION

Accepted Solutions

alexa
New Contributor III

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

2 REPLIES 2

alexa
New Contributor III

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

SergeRielau
Contributor III
Contributor III

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);

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.