freeanna.blogg.se

Here in this article, we have explained the most used functions to calculate the difference in terms of Months, Days, Seconds, Minutes, and Hours.

thanks.give logic working fine.we may give between 0 and 29 that time its take from today to last 30 days. In this article, you have learned Spark SQL datediff() and many other functions to calculate date differences. SELECT FROM product WHERE pdate > DATEADD (day,-30,GETDATE ()) and pdate < getdate () SELECT FROM product WHERE DATEDIFF (day,pdate,GETDATE ()) between 0 and 30. select("current_date", "Date", "hours_between")

withColumn("hours_between", col("minutes_between").cast("bigint")/60) Here we use the same Spark SQL unix_timestamp() to calculate the difference in minutes and then convert the respective difference into HOURS. select("current_date", "Date", "minutes_between") withColumn("minutes_between", col("seconds_between").cast("bigint")/60) Here we use the same Spark SQL unix_timestamp to calculate the difference in seconds and then convert the respective difference into MINUTES. | date|current_date|seconds_between_to_timestamp| Syntax: to_timestamp( timestamp, format])ĭf.withColumn("seconds_between_to_timestamp", (to_timestamp(col("current_date")) - to_timestamp(col("date"))).cast("bigInt")) We can also get the difference between the dates in terms of seconds using to_timestamp() function. | date|current_date|unix_timestamp_current_date|unix_timestamp_date|seconds_between| withColumn("seconds_between", unix_timestamp(col("current_date")) - unix_timestamp(col("date").cast("Date"))) You could simply minus the years but quarters would be more tricky. withColumn("unix_timestamp_date", unix_timestamp(col("date").cast("Date"))) SELECT startDate, endDate, DATEDIFF ( endDate, startDate ) AS diffdays, CAST ( monthsbetween ( endDate, startDate ) AS INT ) AS diffmonths FROM yourTable ORDER BY 1 There are also year and quarter functions for determining the year and quarter of a date respectively. Note: The UNIX timestamp function converts the timestamp into the number of seconds since the first of January 1970.ĭf.withColumn("unix_timestamp_current_date", unix_timestamp(col("current_date"))) For example, because the common calendar starts from the year 1, the first decade (decade 1) is through, and the second decade (decade 2) is through.

Syntax:unix_timestamp( timestamp, TimestampFormat) Amazon Redshift interprets the DECADE or DECADES DATEPART based on the common calendar. Spark SQL provides the months_between() function to calculate the Datediff between the dates the StartDate and EndDate in terms of Months Let’s see with an example.ĭf.withColumn("diff_in_days", datediff(col("date"),col("current_date"))) If not you will end up with a negative difference as below. Note: When using Spark datediff() for date difference, we should make sure to specify the greater or max date as first ( endDate) followed by the lesser or minimum date ( startDate). Df.withColumn("diff_in_days", datediff(col("current_date"), col("date")))