Date and timestamp functions in Apache Spark

Introduction

Date and timestamp functions help us work with dates and times easily. They let us find differences, add time, or format dates in data.

You want to find how many days passed between two dates in a sales report.

You need to add hours or minutes to a timestamp to schedule events.

You want to extract the year or month from a date to group data by time.

You need to convert string dates into date format to analyze them.

You want to get the current date or timestamp for logging or filtering.

Syntax

Apache Spark

from pyspark.sql.functions import current_date, current_timestamp, datediff, date_add, date_sub, year, month, dayofmonth, to_date, unix_timestamp

# Example usage:
df.select(current_date(), current_timestamp())
df.select(datediff(df.end_date, df.start_date))
df.select(date_add(df.date, 5))
df.select(year(df.date))

Use from pyspark.sql.functions import <function_name> to access date functions.

Functions work on columns in Spark DataFrames, not on plain Python dates.

Examples

Gets the current date.

Apache Spark

from pyspark.sql.functions import current_date

df.select(current_date())

Calculates days between two dates.

Apache Spark

from pyspark.sql.functions import datediff

df.select(datediff(df.end_date, df.start_date))

Adds 10 days to a date.

Apache Spark

from pyspark.sql.functions import date_add

df.select(date_add(df.date, 10))

Extracts the year from a date.

Apache Spark

from pyspark.sql.functions import year

df.select(year(df.date))

Sample Program

This program creates a Spark DataFrame with start and end dates. It converts strings to dates, then calculates the difference in days, adds 5 days to the start date, extracts the year, and shows the current date and timestamp.

Apache Spark

from pyspark.sql import SparkSession
from pyspark.sql.functions import current_date, current_timestamp, datediff, date_add, year

spark = SparkSession.builder.appName('DateExample').getOrCreate()

# Create sample data
data = [
    ('2024-01-01', '2024-01-10'),
    ('2024-02-15', '2024-02-20')
]

columns = ['start_date', 'end_date']
df = spark.createDataFrame(data, columns)

# Convert string to date type
from pyspark.sql.functions import to_date

df = df.withColumn('start_date', to_date('start_date'))
       .withColumn('end_date', to_date('end_date'))

# Calculate days difference
result = df.select(
    'start_date',
    'end_date',
    datediff('end_date', 'start_date').alias('days_diff'),
    date_add('start_date', 5).alias('start_plus_5_days'),
    year('start_date').alias('start_year'),
    current_date().alias('today'),
    current_timestamp().alias('now')
)

result.show(truncate=False)

spark.stop()

Important Notes

Make sure date columns are in date format, not string, before using date functions.

Current date and timestamp depend on the system running Spark.

Use to_date() to convert strings to dates.

Summary

Date and timestamp functions help analyze and manipulate time data easily.

Common tasks include finding differences, adding days, and extracting parts like year or month.

Always convert strings to date type before using these functions in Spark.

Practice

1. What does the Spark SQL function current_date() return?

easy

A. The date of the first day of the current month

B. The current timestamp with time part

C. The date of the last day of the current year

D. The current date without time part

Solution

1. Step 1: Understand the function purpose

   current_date() returns the current date only, without any time information.

2. Step 2: Compare with other date/time functions

   current_timestamp() returns date and time, but current_date() returns only the date part.

3. Final Answer:

   The current date without time part -> Option D

4. Quick Check:

   current_date() = date only [OK]

Hint: current_date() gives date only, no time included [OK]

Common Mistakes:

• Confusing current_date() with current_timestamp()
• Expecting time part in current_date() output
• Thinking it returns first or last day of month/year

2. Which of the following is the correct syntax to add 5 days to a date column date_col in Spark DataFrame?

easy

A. df.withColumn('new_date', date_add(df.date_col, 5))

B. df.withColumn('new_date', add_days(df.date_col, 5))

C. df.withColumn('new_date', date_add(5, df.date_col))

D. df.withColumn('new_date', addDays(df.date_col, 5))

Solution

1. Step 1: Recall the correct function name and parameter order

   The Spark function to add days is date_add(date, days), where the first argument is the date column and second is number of days.

2. Step 2: Check syntax correctness

   df.withColumn('new_date', date_add(df.date_col, 5)) uses date_add(df.date_col, 5) which matches the correct function and parameter order.

3. Final Answer:

   df.withColumn('new_date', date_add(df.date_col, 5)) -> Option A

4. Quick Check:

   date_add(date, days) = correct syntax [OK]

Hint: Use date_add(date_col, days) to add days [OK]

Common Mistakes:

• Swapping parameter order in date_add
• Using non-existent function add_days or addDays
• Passing number before date column

3. Given the following Spark code, what will be the output of df.show()?

from pyspark.sql.functions import to_date

data = [("2024-06-10",), ("2024-06-15",)]
df = spark.createDataFrame(data, ["date_str"])
df = df.withColumn("date", to_date(df.date_str))
df.show()

medium

A. +----------+----------+
| date_str | date |
+----------+----------+
|2024-06-10|2024-06-10|
|2024-06-15|2024-06-15|
+----------+----------+

B. +----------+----------+
| date_str | date |
+----------+----------+
|2024-06-10|null |
|2024-06-15|null |
+----------+----------+

C. +----------+----------+
| date_str | date |
+----------+----------+
|2024-06-10|2024/06/10|
|2024-06-15|2024/06/15|
+----------+----------+

D. SyntaxError

Solution

1. Step 1: Understand to_date conversion

   to_date() converts a string in 'yyyy-MM-dd' format to a date type successfully.

2. Step 2: Check the input strings and output

   Input strings are in correct format, so date column will have date values matching the strings.

3. Final Answer:

   DataFrame with date_str and date columns showing matching dates -> Option A

4. Quick Check:

   to_date('yyyy-MM-dd') converts string to date [OK]

Hint: to_date() converts 'yyyy-MM-dd' strings to date type [OK]

Common Mistakes:

• Expecting null output due to format mismatch
• Confusing date format output with slashes instead of dashes
• Thinking code causes syntax error

4. Identify the error in this Spark code snippet that tries to calculate the difference in days between two dates:

from pyspark.sql.functions import datediff

data = [("2024-06-10", "2024-06-15")]
df = spark.createDataFrame(data, ["start", "end"])
df = df.withColumn("diff", datediff(df.end, df.start))
df.show()

medium

A. Error because columns are strings, need to convert to date first

B. No error, code runs correctly and shows difference in days

C. datediff function parameters are reversed

D. datediff only works with timestamps, not dates

Solution

1. Step 1: Check data types of columns

   Columns "start" and "end" are strings in standard date format ('yyyy-MM-dd'), and Spark's datediff() can parse them implicitly without explicit conversion.

2. Step 2: Verify function usage

   datediff(df.end, df.start) has the correct parameter order (end first, start second) and computes the positive difference in days.

3. Final Answer:

   No error, code runs correctly and shows difference in days -> Option B

4. Quick Check:

   datediff on date-formatted strings works [OK]

Hint: datediff works on properly formatted date strings [OK]

Common Mistakes:

• Thinking strings must be explicitly converted with to_date()
• Reversing datediff parameters
• Believing datediff requires timestamps only

5. You have a Spark DataFrame with a timestamp column event_time. You want to create a new column event_hour that extracts the hour of the day (0-23) from the timestamp. Which code snippet correctly achieves this?

hard

A. df.withColumn('event_hour', date_format(df.event_time, 'HH'))

B. df.withColumn('event_hour', extract_hour(df.event_time))

C. df.withColumn('event_hour', hour(df.event_time))

D. df.withColumn('event_hour', substring(df.event_time, 12, 2))

Solution

1. Step 1: Identify the correct function to extract hour

   The Spark function hour() extracts the hour (0-23) from a timestamp column.

2. Step 2: Validate other options

   extract_hour() does not exist; date_format() returns string, not integer; substring() on timestamp string is error-prone.

3. Final Answer:

   df.withColumn('event_hour', hour(df.event_time)) -> Option C

4. Quick Check:

   Use hour() to get hour from timestamp [OK]

Hint: Use hour() function to extract hour from timestamp [OK]

Common Mistakes:

• Using non-existent extract_hour function
• Using date_format returns string, not integer
• Using substring on timestamp string format