CODEWITHSUNDEEP
sqlscalaapache-sparkapache-spark-sql
Cauder
Cauder

Reputation: 2597

How do I do date diff in a spark sql environment?

I have a table with a creation date and an action date. I'd like to get the number number of minutes between the two dates. I looked at the docs and I'm having trouble finding a solution.

%sql
SELECT datediff(creation_dt, actions_dt)
FROM actions
limit 10

This gives me the number of days between the two dates. One record looks like

2019-07-31 23:55:22.0  |  2019-07-31 23:55:21 | 0

How can I get the number of minutes?

Upvotes: 1

Views: 359

Answers (1)

blairmeister
blairmeister

Reputation: 915

As stated in the comments, if you are using Spark or Pyspark then the withColumn method is best.

BUT

If you are using the SparkSQL environment then you could use the unix_timestamp() function to get what you need

select ((unix_timestamp('2019-09-09','yyyy-MM-dd') - unix_timestamp('2018-09-09','yyyy-MM-dd'))/60);

Swap the dates with your column names and define what your date pattern is as the parameters.

Both dates are converted into seconds and the difference is taken. We then divide by 60 to get the minutes.

525600.0

Upvotes: 1

Related Questions