Date and timestamp functions in Apache Spark - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the current_date() function return in Apache Spark?

The current_date() function returns the current date as a DateType value without the time part.

beginner

How do you extract the year from a timestamp column in Spark?

Use the year() function to get the year part from a timestamp or date column.

intermediate

What is the difference between unix_timestamp() and to_timestamp() functions?

unix_timestamp() returns the number of seconds since 1970-01-01 00:00:00 UTC as a long integer, while to_timestamp() converts a string to a timestamp type.

beginner

How can you add 5 days to a date column in Spark?

Use the date_add(dateColumn, 5) function to add 5 days to the date in dateColumn.

beginner

What does the datediff(end, start) function compute?

It calculates the number of days between two dates: end minus start.

Which function returns the current timestamp in Spark?

A current_date()

B current_timestamp()

C unix_timestamp()

D to_date()

current_timestamp() returns the current date and time as a timestamp.

How do you convert a string like '2024-06-01 12:30:00' to a timestamp in Spark?

A date_add('2024-06-01 12:30:00', 1)

B unix_timestamp('2024-06-01 12:30:00')

C to_date('2024-06-01 12:30:00')

D to_timestamp('2024-06-01 12:30:00')

to_timestamp() converts a string to a timestamp type.

What does date_sub(dateColumn, 3) do?

A Subtracts 3 days from dateColumn

B Returns the day of the month

C Adds 3 days to dateColumn

D Converts dateColumn to string

date_sub() subtracts days from a date.

Which function gives the number of seconds since 1970-01-01 00:00:00 UTC?

A year()

B current_date()

C unix_timestamp()

D to_timestamp()

unix_timestamp() returns seconds since epoch.

If you want to find the difference in days between two dates, which function do you use?

A datediff()

B date_add()

C date_sub()

D year()

datediff() calculates days between two dates.

Explain how to convert a string to a timestamp and then extract the year in Apache Spark.

Think about the functions to convert and then extract parts of a date.

Describe how to calculate the number of days between two date columns in Spark.

Focus on the function that measures days difference.

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