cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
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
Valued Contributor
Valued Contributor

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

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!