Dates and timestamps

Parse customer signup text, filter calendar dates, and calculate days since signup.

Step 1 of 6 · Learn

Parse signup text

The setup builds customer_signups from the directory and assigns September 1–6 at 10:30 to its six IDs for this exercise. These dates are not fields in customers. to_date keeps the calendar date; to_timestamp also keeps the time. The session timezone is UTC. All inputs here are valid; malformed input needs a deliberate parsing policy.

Lesson reference: PySpark and Scala

Parse signup text

PySpark example

from pyspark.sql.functions import (
    col, lit, concat, trim, lower, upper, regexp_extract,
    format_string, to_date, to_timestamp, datediff
)

spark.conf.set("spark.sql.session.timeZone", "UTC")
customer_signups = customers.select(
    "customer_id",
    format_string("2026-09-%02d 10:30:00", col("customer_id") - 100).alias("signup_text")
)

result = customer_signups.select(
    col("customer_id"),
    to_date(col("signup_text"), "yyyy-MM-dd HH:mm:ss").alias("signed_up_on")
).orderBy("customer_id")
result.show()

Scala example

import org.apache.spark.sql.functions._

spark.conf.set("spark.sql.session.timeZone", "UTC")
val customer_signups = customers.select(
  col("customer_id"),
  format_string("2026-09-%02d 10:30:00", col("customer_id") - 100).alias("signup_text")
)

val result = customer_signups.select(
    col("customer_id"),
    to_date(col("signup_text"), "yyyy-MM-dd HH:mm:ss").alias("signed_up_on")
).orderBy("customer_id")
result.show()

Filter signup dates

Parse text before comparing dates. This example keeps September 4 and later, including the boundary. Compare against a date literal instead of depending on text ordering. Filtering this working DataFrame does not remove customers from the directory.

PySpark example

from pyspark.sql.functions import (
    col, lit, concat, trim, lower, upper, regexp_extract,
    format_string, to_date, to_timestamp, datediff
)

spark.conf.set("spark.sql.session.timeZone", "UTC")
customer_signups = customers.select(
    "customer_id",
    format_string("2026-09-%02d 10:30:00", col("customer_id") - 100).alias("signup_text")
)

result = customer_signups.withColumn(
    "signed_up_on", to_date(col("signup_text"), "yyyy-MM-dd HH:mm:ss")
).filter(col("signed_up_on") >= lit("2026-09-04").cast("date")).select("customer_id", "signed_up_on").orderBy("customer_id")
result.show()

Scala example

import org.apache.spark.sql.functions._

spark.conf.set("spark.sql.session.timeZone", "UTC")
val customer_signups = customers.select(
  col("customer_id"),
  format_string("2026-09-%02d 10:30:00", col("customer_id") - 100).alias("signup_text")
)

val result = customer_signups.withColumn(
    "signed_up_on", to_date(col("signup_text"), "yyyy-MM-dd HH:mm:ss")
).filter(col("signed_up_on") >= lit("2026-09-04").cast("date")).select("customer_id", "signed_up_on").orderBy("customer_id")
result.show()

Days since signup

datediff(end, start) counts calendar-day boundaries, not elapsed 24-hour periods. September 10 is a fixed reporting date, making the example repeatable. Reversing the arguments reverses the sign.

PySpark example

from pyspark.sql.functions import (
    col, lit, concat, trim, lower, upper, regexp_extract,
    format_string, to_date, to_timestamp, datediff
)

spark.conf.set("spark.sql.session.timeZone", "UTC")
customer_signups = customers.select(
    "customer_id",
    format_string("2026-09-%02d 10:30:00", col("customer_id") - 100).alias("signup_text")
)

result = customer_signups.select(
    col("customer_id"),
    datediff(lit("2026-09-10").cast("date"),
        to_date(col("signup_text"), "yyyy-MM-dd HH:mm:ss")).alias("days_elapsed")
).orderBy("customer_id")
result.show()

Scala example

import org.apache.spark.sql.functions._

spark.conf.set("spark.sql.session.timeZone", "UTC")
val customer_signups = customers.select(
  col("customer_id"),
  format_string("2026-09-%02d 10:30:00", col("customer_id") - 100).alias("signup_text")
)

val result = customer_signups.select(
    col("customer_id"),
    datediff(lit("2026-09-10").cast("date"),
        to_date(col("signup_text"), "yyyy-MM-dd HH:mm:ss")).alias("days_elapsed")
).orderBy("customer_id")
result.show()

example.pyPySpark

from pyspark.sql.functions import ( col, lit, concat, trim, lower, upper, regexp_extract, format_string, to_date, to_timestamp, datediff ) spark.conf.set("spark.sql.session.timeZone", "UTC") customer_signups = customers.select( "customer_id", format_string("2026-09-%02d 10:30:00", col("customer_id") - 100).alias("signup_text") ) result = customer_signups.select( col("customer_id"), to_date(col("signup_text"), "yyyy-MM-dd HH:mm:ss").alias("signed_up_on") ).orderBy("customer_id") result.show()

1. Prepare customer_signupsAdd the exercise signup text to a separate DataFrame.

2. Parse signup textApply the displayed expression to the prepared input.

3. Inspect the resultRead dates and times using the explicit UTC session setting.

Source customer_signups6 rows

customer_id	signup_text
101	2026-09-01 10:30:00
102	2026-09-02 10:30:00
103	2026-09-03 10:30:00
104	2026-09-04 10:30:00
105	2026-09-05 10:30:00
106	2026-09-06 10:30:00

Parse signup text

Result6 rows

customer_id	signed_up_on
101	2026-09-01
102	2026-09-02
103	2026-09-03
104	2026-09-04
105	2026-09-05
106	2026-09-06

Compare the source columns with the transformed result.

The example is loaded in the editor. Run it as written, then try a small change.

Runs on the Spark backend. First startup may take a moment.

Run your code to see the result.