Introduction
In this lesson, we will explore the various date and timestamp functions available in Spark SQL. Handling dates is a critical aspect of data engineering as it allows us to perform time-based analysis and aggregations in our datasets. Knowing the appropriate functions to use and when to use them can streamline data manipulations and enhance the efficiency of your Spark applications.
Importance of Date Functions
Date functions are essential because they help manipulate and analyze time-related data across various scenarios, such as data cleansing, time-series analysis, and event tracking. They also ensure that date comparisons and calculations are accurate, which is critical in any analytical task, especially when forecasting trends or identifying temporal patterns.
Most Used Date Functions
Below is a list of some of the most used date functions in Spark SQL:
- current_date(): Returns the current date.
- current_timestamp(): Returns the current timestamp.
- date_format(date, format): Converts a date to a specific format.
- to_date(string, format): Converts a string to a date.
- datediff(endDate, startDate): Returns the difference in days between two dates.
- add_days(startDate, days): Adds a specified number of days to a date.
- sub_days(startDate, days): Subtracts a specified number of days from a date.
- date_add(startDate, days): Adds a specified number of days to a date.
- date_sub(startDate, days): Subtracts a specified number of days from a date.
When to Use Date Functions
These functions are employed in a variety of scenarios:
- Data enrichment: When there's a need to create additional temporal fields, such as extracting year, month, or day.
- Data filtering: Using date conditions to select subsets of data from time windows.
- Calculating durations: When it’s necessary to find time intervals between records, such as analyzing the length of events.
Spark Date Functions Examples
Below are the most used examples of Date Functions.
current_date() and date_format()
We will see how to get the current date and convert date into a specific date format using date_format() with a Scala example. The example below parses the date and converts it from ‘yyyy-dd-mm’ to ‘MM-dd-yyyy’ format.
import org.apache.spark.sql.functions._
Seq(("2019-01-23"))
.toDF("Input")
.select(
current_date().as("current_date"),
col("Input"),
date_format(col("Input"), "MM-dd-yyyy").as("format")
).show()
output
+------------+----------+-----------+
|current_date| Input| format|
+------------+----------+-----------+
| 2019-07-23|2019-01-23| 01-23-2019|
+------------+----------+-----------+
to_date()
The below example converts the string in date format ‘MM/dd/yyyy’ to a DateType ‘yyyy-MM-dd’ using to_date().
import org.apache.spark.sql.functions._
Seq(("04/13/2019"))
.toDF("Input")
.select(
col("Input"),
to_date(col("Input"), "MM/dd/yyyy").as("to_date")
).show()
output
+----------+----------+
| Input| to_date|
+----------+----------+
|04/13/2019|2019-04-13|
+----------+----------+
datediff()
The below example returns the difference between two dates using datediff() with a Scala example.
import org.apache.spark.sql.functions._
Seq(("2019-01-23"),("2019-06-24"),("2019-09-20"))
.toDF("input")
.select(
col("input"),
current_date(),
datediff(current_date(), col("input")).as("diff")
).show()
output
+----------+--------------+------+
| input|current_date()| diff|
+----------+--------------+------+
|2019-01-23| 2019-07-23| 181|
|2019-06-24| 2019-07-23| 29|
|2019-09-20| 2019-07-23| -59|
+----------+--------------+------+
months_between()
Below example returns the months between two dates using months_between() with Scala language.
import org.apache.spark.sql.functions._
Seq(("2019-01-23"),("2019-06-24"),("2019-09-20"))
.toDF("date")
.select(
col("date"),
current_date(),
datediff(current_date(), col("date")).as("datediff"),
months_between(current_date(), col("date")).as("months_between")
).show()
output
+----------+--------------+--------+--------------+
| date|current_date()|datediff|months_between|
+----------+--------------+--------+--------------+
|2019-01-23| 2019-07-23| 181| 6.0|
|2019-06-24| 2019-07-23| 29| 0.96774194|
|2019-09-20| 2019-07-23| -59| -1.90322581|
+----------+--------------+--------+--------------+
trunc()
The below example truncates the date at a specified unit using trunc() with Scala language.
import org.apache.spark.sql.functions._
Seq(("2019-01-23"),("2019-06-24"),("2019-09-20"))
.toDF("input")
.select(
col("input"),
trunc(col("input"), "Month").as("Month_Trunc"),
trunc(col("input"), "Year").as("Month_Year"),
trunc(col("input"), "Month").as("Month_Trunc")
).show()
output
+----------+-----------+----------+-----------+
| input|Month_Trunc|Month_Year|Month_Trunc|
+----------+-----------+----------+-----------+
|2019-01-23| 2019-01-01|2019-01-01| 2019-01-01|
|2019-06-24| 2019-06-01|2019-01-01| 2019-06-01|
|2019-09-20| 2019-09-01|2019-01-01| 2019-09-01|
+----------+-----------+----------+-----------+
add_months(), date_add(), date_sub()
Here, I am adding and subtracting the date and month from a given input.
import org.apache.spark.sql.functions._
Seq(("2019-01-23"),("2019-06-24"),("2019-09-20"))
.toDF("input")
.select(
col("input"),
add_months(col("input"), 3).as("add_months"),
add_months(col("input"), -3).as("sub_months"),
date_add(col("input"), 4).as("date_add"),
date_sub(col("input"), 4).as("date_sub")
).show()
output
+----------+----------+----------+----------+----------+
| input|add_months|sub_months| date_add| date_sub|
+----------+----------+----------+----------+----------+
|2019-01-23|2019-04-23|2018-10-23|2019-01-27|2019-01-19|
|2019-06-24|2019-09-24|2019-03-24|2019-06-28|2019-06-20|
|2019-09-20|2019-12-20|2019-06-20|2019-09-24|2019-09-16|
+----------+----------+----------+----------+----------+
year(), month(), dayofweek(), dayofmonth(), dayofyear(), next_day(), weekofyear()
import org.apache.spark.sql.functions._
Seq(("2019-01-23"),("2019-06-24"),("2019-09-20"))
.toDF("input")
.select(
col("input"),
year(col("input")).as("year"),
month(col("input")).as("month"),
dayofweek(col("input")).as("dayofweek"),
dayofmonth(col("input")).as("dayofmonth"),
dayofyear(col("input")).as("dayofyear"),
next_day(col("input"), "Sunday").as("next_day"),
weekofyear(col("input")).as("weekofyear")
).show()
output
+----------+----+-----+---------+----------+---------+----------+----------+
| input|year|month|dayofweek|dayofmonth|dayofyear| next_day|weekofyear|
+----------+----+-----+---------+----------+---------+----------+----------+
|2019-01-23|2019| 1| 4| 23| 23|2019-01-27| 4|
|2019-06-24|2019| 6| 2| 24| 175|2019-06-30| 26|
|2019-09-20|2019| 9| 6| 20| 263|2019-09-22| 38|
+----------+----+-----+---------+----------+---------+----------+----------+
Summary
In this lesson, we have covered the core date functions available in Spark SQL. We discussed their importance in data handling, identified the most commonly used date functions, and provided examples of how to convert string values to date types and format dates as strings. Understanding these functions is vital for effective date manipulation and analysis in your data engineering tasks.