Date and timestamp functions in Apache Spark - Mini Project: Build & Apply

Date and timestamp functions

📖 Scenario: You work as a data analyst for a retail company. You have a dataset of sales transactions with timestamps. You want to analyze the data by extracting useful date and time information.

🎯 Goal: Build a Spark DataFrame with sales data, add a configuration for the date format, use Spark date and timestamp functions to extract year and month, and display the results.

📋 What You'll Learn

Create a Spark DataFrame with sales data including a timestamp column

Add a configuration variable for the date format string

Use Spark SQL functions to extract the year and month from the timestamp

Display the resulting DataFrame with the extracted date parts

💡 Why This Matters

🌍 Real World

Retail companies often analyze sales data by date to find trends and patterns over time.

💼 Career

Data analysts and data scientists use date and timestamp functions to prepare and analyze time-based data for reports and decision making.

1

Create the sales DataFrame

Create a Spark DataFrame called sales_df with these exact columns and data: transaction_id (integers 1 to 3), amount (floats 100.0, 150.5, 200.75), and timestamp (strings '2023-05-01 10:15:00', '2023-06-15 12:30:00', '2023-07-20 14:45:00'). Use spark.createDataFrame() with a list of tuples and a schema.

Apache Spark

# Create sales_df DataFrame with transaction_id, amount, and timestamp columns
# Your code here

Hint

Use spark.createDataFrame() with a list of tuples and a schema defined by StructType and StructField.

2

Add date format configuration

Create a variable called date_format and set it to the string 'yyyy-MM-dd HH:mm:ss' to match the timestamp format in sales_df.

Apache Spark

from pyspark.sql import SparkSession
from pyspark.sql.types import StructType, StructField, IntegerType, FloatType, StringType

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

schema = StructType([
    StructField('transaction_id', IntegerType(), False),
    StructField('amount', FloatType(), False),
    StructField('timestamp', StringType(), False)
])
data = [
    (1, 100.0, '2023-05-01 10:15:00'),
    (2, 150.5, '2023-06-15 12:30:00'),
    (3, 200.75, '2023-07-20 14:45:00')
]
sales_df = spark.createDataFrame(data, schema)

# Create date_format variable with the string 'yyyy-MM-dd HH:mm:ss'
# Your code here

Hint

Assign the exact string 'yyyy-MM-dd HH:mm:ss' to the variable date_format.

3

Extract year and month from timestamp

Import to_timestamp, year, and month from pyspark.sql.functions. Create a new DataFrame called sales_with_date by adding two new columns to sales_df: year and month. Use to_timestamp with date_format to convert the timestamp string to a timestamp type, then extract the year and month.

Apache Spark

from pyspark.sql import SparkSession
from pyspark.sql.types import StructType, StructField, IntegerType, FloatType, StringType
from pyspark.sql.functions import to_timestamp, year, month

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

schema = StructType([
    StructField('transaction_id', IntegerType(), False),
    StructField('amount', FloatType(), False),
    StructField('timestamp', StringType(), False)
])
data = [
    (1, 100.0, '2023-05-01 10:15:00'),
    (2, 150.5, '2023-06-15 12:30:00'),
    (3, 200.75, '2023-07-20 14:45:00')
]
sales_df = spark.createDataFrame(data, schema)

date_format = 'yyyy-MM-dd HH:mm:ss'

# Create sales_with_date DataFrame adding year and month columns
# Your code here

Hint

Use withColumn twice to add year and month columns after converting timestamp to timestamp type.

4

Display the result

Use print() and sales_with_date.show() to display the DataFrame with the extracted year and month columns.

Apache Spark

from pyspark.sql import SparkSession
from pyspark.sql.types import StructType, StructField, IntegerType, FloatType, StringType
from pyspark.sql.functions import to_timestamp, year, month

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

schema = StructType([
    StructField('transaction_id', IntegerType(), False),
    StructField('amount', FloatType(), False),
    StructField('timestamp', StringType(), False)
])
data = [
    (1, 100.0, '2023-05-01 10:15:00'),
    (2, 150.5, '2023-06-15 12:30:00'),
    (3, 200.75, '2023-07-20 14:45:00')
]
sales_df = spark.createDataFrame(data, schema)

date_format = 'yyyy-MM-dd HH:mm:ss'

sales_with_date = sales_df.withColumn('ts', to_timestamp('timestamp', date_format)) \
    .withColumn('year', year('ts')) \
    .withColumn('month', month('ts')) \
    .drop('ts')

# Print the sales_with_date DataFrame
# Your code here

Hint

Use print() to show a message and sales_with_date.show() to display the DataFrame.

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