Date and timestamp functions in Apache Spark - Step-by-Step Execution

Concept Flow - Date and timestamp functions

Start with DataFrame

↓

Select date/timestamp column

↓

Apply date/timestamp function

↓

Get new column with result

↓

Use result for analysis or display

↓

End

We start with a DataFrame, pick a date or timestamp column, apply a function to transform or extract info, then use the result.

Execution Sample

Apache Spark

from pyspark.sql.functions import year, month, dayofmonth

# Extract year, month, day from date column
df.select(year('date').alias('year'), month('date').alias('month'), dayofmonth('date').alias('day')).show()

This code extracts year, month, and day parts from a date column in a Spark DataFrame.

Execution Table

Step	Input Row (date)	Function Applied	Output Columns (year, month, day)
1	2023-06-15	year('date')	2023
1	2023-06-15	month('date')	6
1	2023-06-15	dayofmonth('date')	15
2	2022-12-01	year('date')	2022
2	2022-12-01	month('date')	12
2	2022-12-01	dayofmonth('date')	1
3	2024-01-31	year('date')	2024
3	2024-01-31	month('date')	1
3	2024-01-31	dayofmonth('date')	31
Exit	No more rows	N/A	All rows processed

💡 All rows processed, no more data to extract date parts.

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	Final
date	N/A	2023-06-15	2022-12-01	2024-01-31	Last row value
year	N/A	2023	2022	2024	Last extracted year
month	N/A	6	12	1	Last extracted month
day	N/A	15	1	31	Last extracted day

Key Moments - 3 Insights

Why do we call year('date') instead of just year?

year() is a function that needs a column input to know which date to extract the year from, as shown in execution_table rows 1-3.

What happens if the date column has null values?

The functions will return null for those rows, so no year, month, or day will be extracted, similar to how output columns depend on input values in execution_table.

Can we extract time parts like hour or minute similarly?

Yes, Spark has functions like hour() and minute() that work like year(), shown by analogy to the date part extraction in execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, what is the month extracted from the date '2022-12-01'?

A 1

B 6

C 12

D 15

💡 Hint

Check row 5 in execution_table where input date is '2022-12-01' and function is month('date').

At which step does the day extracted equal 31?

A Step 1

B Step 3

C Step 2

D Exit

💡 Hint

Look at execution_table rows 8-9 for dayofmonth('date') outputs.

If the date column had a null value at row 2, what would be the year output at that step?

A null

B 0

C 2022

D Error

💡 Hint

Refer to key_moments about null handling and execution_table logic for function outputs.

Concept Snapshot

Date and timestamp functions in Spark extract parts like year, month, day from date columns.
Use functions like year(col), month(col), dayofmonth(col).
They return integers or null if input is null.
Apply on DataFrame columns to create new columns.
Useful for time-based analysis and filtering.

Full Transcript

This visual execution trace shows how Spark date and timestamp functions work step-by-step. We start with a DataFrame containing a date column. For each row, we apply functions like year(), month(), and dayofmonth() to extract parts of the date. The execution table shows input dates and the output values for each function. Variables track how values change after processing each row. Key moments clarify why functions need column inputs, how nulls are handled, and that time parts can be extracted similarly. The quiz tests understanding of outputs at specific steps and null behavior. The snapshot summarizes the key usage points for 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