Date and timestamp functions in Apache Spark - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to extract the year from the 'date' column.

Apache Spark

from pyspark.sql.functions import [1]
df.select([1]("date")).show()

Drag options to blanks, or click blank then click option'

Ayear

Bhour

Cdayofmonth

Dmonth

Attempts:

3 left

💡 Hint

Common Mistakes

Using month or dayofmonth instead of year.

Forgetting to import the function.

The year function extracts the year part from a date or timestamp column.

2fill in blank

medium

Complete the code to add 5 days to the 'date' column.

Apache Spark

from pyspark.sql.functions import date_add
df.select(date_add("date", [1])).show()

Drag options to blanks, or click blank then click option'

A5

B3

C10

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using a wrong number of days.

Passing the number as a string instead of an integer.

The date_add function adds the specified number of days to a date column. Here, 5 days are added.

3fill in blank

hard

Fix the error in the code to calculate the difference in days between 'date1' and 'date2'.

Apache Spark

from pyspark.sql.functions import datediff
df.select(datediff("date1", [1])).show()

Drag options to blanks, or click blank then click option'

A"date1"

Bdate1

C"date2"

Ddate2

Attempts:

3 left

💡 Hint

Common Mistakes

Passing a variable instead of a string.

Using the same column name twice.

The datediff function needs two date columns. The second argument should be the string name of the second date column, here "date2".

4fill in blank

hard

Fill both blanks to create a new column 'hour' extracting the hour from 'timestamp' and filter rows where hour is greater than 12.

Apache Spark

from pyspark.sql.functions import [1]
df.select([1]("timestamp").alias("hour")).filter("hour [2] 12").show()

Drag options to blanks, or click blank then click option'

Ahour

B>

C<

Dminute

Attempts:

3 left

💡 Hint

Common Mistakes

Using minute instead of hour.

Using less than instead of greater than in filter.

The hour function extracts the hour from a timestamp. The filter keeps rows where hour is greater than 12.

5fill in blank

hard

Fill all three blanks to format the 'date' column as 'yyyy-MM' into a new column 'month_year' and filter rows where month_year > '2023-06'.

Apache Spark

from pyspark.sql.functions import [1]
df.withColumn("month_year", [1]([2], [3])).filter("month_year > '2023-06'").show()

Drag options to blanks, or click blank then click option'

Adate_format

B"date"

C"yyyy-MM"

Dyear

Attempts:

3 left

💡 Hint

Common Mistakes

Using an extraction function like year instead of date_format.

Forgetting quotes around the column name or format string.

Using incorrect format pattern.

The date_format function formats a date or timestamp column according to the specified format string, here extracting year and month as 'yyyy-MM'.

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