Introduction

In this lesson, we will explore the various date and timestamp functions available in PySpark SQL. Handling dates is a critical aspect of data engineering as it allows us to perform date 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 date 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 PySpark 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.

PySpark 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 a date into a specific date format using date_format() with a PySpark example. The example below parses the date and converts it from ‘yyyy-dd-mm’ to ‘MM-dd-yyyy’ format.

from pyspark.sql import SparkSession
from pyspark.sql.functions import current_date, col, date_format

spark = SparkSession.builder.appName("DateFunctions").getOrCreate()

data = [("2019-01-23",)]
df = spark.createDataFrame(data, ["Input"])
df.select(
    current_date().alias("current_date"),
    col("Input"),
    date_format(col("Input"), "MM-dd-yyyy").alias("format")
).show()

+------------+----------+-----------+
|current_date|     Input|     format|
+------------+----------+-----------+
|  2023-10-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().

from pyspark.sql.functions import to_date

data = [("04/13/2019",)]
df = spark.createDataFrame(data, ["Input"])
df.select(
    col("Input"),
    to_date(col("Input"), "MM/dd/yyyy").alias("to_date")
).show()

+----------+----------+
|     Input|   to_date|
+----------+----------+
|04/13/2019|2019-04-13|
+----------+----------+

datediff()

The below example returns the difference between two dates using datediff() with a PySpark example.

from pyspark.sql.functions import datediff

data = [("2019-01-23",),("2019-06-24",),("2019-09-20",)]
df = spark.createDataFrame(data, ["input"])
df.select(
    col("input"),
    current_date(),
    datediff(current_date(), col("input")).alias("diff")
).show()

+----------+--------------+------+
|     input|current_date()|  diff|
+----------+--------------+------+
|2019-01-23|    2023-10-23|  1739|
|2019-06-24|    2023-10-23|  1587|
|2019-09-20|    2023-10-23|  1564|
+----------+--------------+------+

months_between()

Below example returns the months between two dates using months_between() with PySpark.

from pyspark.sql.functions import months_between

data = [("2019-01-23",),("2019-06-24",),("2019-09-20",)]
df = spark.createDataFrame(data, ["date"])
df.select(
    col("date"),
    current_date(),
    datediff(current_date(), col("date")).alias("datediff"),
    months_between(current_date(), col("date")).alias("months_between")
).show()

+----------+--------------+--------+--------------+
|      date|current_date()|datediff|months_between|
+----------+--------------+--------+--------------+
|2019-01-23|    2023-10-23|    1739|          57.0|
|2019-06-24|    2023-10-23|    1587|          52.0|
|2019-09-20|    2023-10-23|    1564|          49.0|
+----------+--------------+--------+--------------+

trunc()

The below example truncates the date at a specified unit using trunc() with PySpark.

from pyspark.sql.functions import trunc

data = [("2019-01-23",),("2019-06-24",),("2019-09-20",)]
df = spark.createDataFrame(data, ["input"])
df.select(
    col("input"),
    trunc(col("input"), "Month").alias("Month_Trunc"),
    trunc(col("input"), "Year").alias("Month_Year")
).show()

+----------+-----------+----------+
|     input|Month_Trunc|Month_Year|
+----------+-----------+----------+
|2019-01-23| 2019-01-01|2019-01-01|
|2019-06-24| 2019-06-01|2019-01-01|
|2019-09-20| 2019-09-01|2019-01-01|
+----------+-----------+----------+

add_months(), date_add(), date_sub()

Here, we are adding and subtracting the date and month from a given input.

from pyspark.sql.functions import add_months, date_add, date_sub

data = [("2019-01-23",),("2019-06-24",),("2019-09-20",)]
df = spark.createDataFrame(data, ["input"])
df.select(
    col("input"),
    add_months(col("input"), 3).alias("add_months"),
    add_months(col("input"), -3).alias("sub_months"),
    date_add(col("input"), 4).alias("date_add"),
    date_sub(col("input"), 4).alias("date_sub")
).show()

+----------+----------+----------+----------+----------+
|     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()

from pyspark.sql.functions import year, month, dayofweek, dayofmonth, dayofyear, next_day, weekofyear

data = [("2019-01-23",),("2019-06-24",),("2019-09-20",)]
df = spark.createDataFrame(data, ["input"])
df.select(
    col("input"),
    year(col("input")).alias("year"),
    month(col("input")).alias("month"),
    dayofweek(col("input")).alias("dayofweek"),
    dayofmonth(col("input")).alias("dayofmonth"),
    dayofyear(col("input")).alias("dayofyear"),
    next_day(col("input"), "Sun").alias("next_day"),
    weekofyear(col("input")).alias("weekofyear")
).show()

+----------+----+-----+---------+----------+---------+----------+----------+
|     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 PySpark 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.