Spark SQL Date & Timestamp Functions

1. Understanding Date and Timestamp Types

Before we manipulate temporal data, it's important to understand the two primary data types Spark uses:

• DateType: Represents a calendar date with year, month, and day components. It does not include time or timezone information. The default string format is yyyy-MM-dd.
• TimestampType: Represents a specific moment in time, including date and time components down to microsecond precision. Crucially, Spark timestamps are stored as UTC (Coordinated Universal Time). When you display them, Databricks converts them to your browser's local timezone, but all calculations on the backend are performed in UTC. This ensures consistency when working with data from different geographical regions. The default format is yyyy-MM-dd HH:mm:ss.SSSSSS.

You can find a quick reference for these and other functions in the following resource. It's a useful page to bookmark for future use.

2. Parsing Strings and Formatting Dates

The most common date-related tasks are converting strings into dates and formatting dates back into strings.

From String to Date/Timestamp: to_date() and to_timestamp()

Source data often contains dates as strings, like "08/25/2023" or "25-Aug-2023 10:30 AM". To use these for calculations, you must first parse them into the correct data type.

• to_date(string_col, format): Parses a string into a DateType.
• to_timestamp(string_col, format): Parses a string into a TimestampType.

The second argument, format, is a string that specifies the pattern of the input. If your string is already in the default format (yyyy-MM-dd), you can omit the format argument.

Example:

SELECT
  to_date('2023-11-20') AS default_format,
  to_timestamp('25/08/2023 14:30:00', 'dd/MM/yyyy HH:mm:ss') AS custom_format;

From Date/Timestamp to String: date_format()

Conversely, you might need to convert a date or timestamp into a specific string format for reporting or creating partition keys.

• date_format(date_col, format): Formats a date or timestamp value into a string based on the provided pattern.

This image provides an excellent visual guide to the different format specifiers you can use.

3. Extracting Components and Performing Arithmetic

Once your data is in the correct DateType or TimestampType, you can easily extract parts from it or perform calculations.

The following video demonstrates many of these key functions. While the examples use PySpark syntax, the function names and logic are identical in Spark SQL.

Let's summarize the key functions for extraction and arithmetic:

• Extraction: year(), quarter(), month(), dayofmonth(), dayofweek(), hour(), minute(), second().
• Arithmetic:
  • date_add(start_date, num_days) / date_sub(start_date, num_days): Adds or subtracts days from a date.
  • add_months(start_date, num_months): Adds or subtracts months.
  • datediff(end_date, start_date): Returns the number of days between two dates.
  • months_between(end_date, start_date): Returns the number of months between two timestamps.
• Current Time:
  • current_date(): Returns the current date (as DateType).
  • current_timestamp() or now(): Returns the current timestamp (as TimestampType).

For a more detailed, SQL-centric reference with code examples, the article below is very helpful.

4. Hands-On Practice: Analyzing User Login Events

Let's apply these functions to a practical scenario. Imagine you have a table of user login events where the timestamp is logged as a string in a non-standard format. Your task is to parse this data, extract useful information, and perform some basic calculations.

Instructions:
Open a Databricks SQL notebook and execute the following queries.

Step 1: Create Sample Data
First, create a table with login events. Notice the event_time is a string.

CREATE OR REPLACE TABLE login_events (
  user_id INT,
  event_time STRING
);

INSERT INTO login_events VALUES
  (101, '2023-10-22 08:30:15'),
  (102, '2023-11-01 23:55:02'),
  (101, '2023-11-15 12:05:45'),
  (103, '2023-11-18 09:00:10');

Step 2: Transform the Data
Now, run the following query. We will use CTEs to build our transformation logic step-by-step, a good practice you learned in the previous lesson.

WITH
  -- Step 1: Parse the string event_time into a proper timestamp.
  parsed_events AS (
    SELECT
      user_id,
      to_timestamp(event_time, 'yyyy-MM-dd HH:mm:ss') AS event_ts
    FROM
      login_events
  ),
  -- Step 2: Extract components and perform calculations.
  analyzed_events AS (
    SELECT
      user_id,
      event_ts,
      -- Extract just the date part
      to_date(event_ts) AS event_date,
      -- Extract the hour of the day
      hour(event_ts) AS login_hour,
      -- Calculate how many days ago the event occurred
      datediff(current_date(), to_date(event_ts)) AS days_ago,
      -- Format the timestamp for a report (e.g., "Sunday, Nov 15")
      date_format(event_ts, 'EEEE, MMM d') AS formatted_date
    FROM
      parsed_events
  )
-- Final Step: Select the results to display.
SELECT
  *
FROM
  analyzed_events
ORDER BY
  event_ts;

Analysis of the Query:

1. parsed_events CTE: The first and most critical step is using to_timestamp() to convert the event_time string into a TimestampType column named event_ts. We provide the format string that matches our input.
2. analyzed_events CTE: With event_ts as a proper timestamp, we can now work with it:
   • We use to_date() to cast the timestamp to a date, effectively dropping the time part.
   • hour() extracts the hour as a number (0-23), which could be useful for analyzing peak login times.
   • datediff(current_date(), ...) calculates the number of days between today and the event date.
   • date_format() creates a human-readable string representation of the date.

This single query successfully reads raw string data, enriches it with extracted and calculated temporal features, and formats it for reporting—a complete and common data engineering workflow.

Conclusion

You have now learned the essential Spark SQL functions for handling dates and timestamps. These functions are indispensable for transforming raw data into a format suitable for analytics, reporting, and building reliable data pipelines.

Key Takeaways

• Parse First: Always convert string representations of dates and times into DateType or TimestampType using to_date() or to_timestamp() as your first step.
• Know Your Functions:
  • Parsing/Formatting: to_date(), to_timestamp(), date_format().
  • Extraction: year(), month(), dayofmonth(), hour().
  • Arithmetic: date_add(), date_sub(), datediff().
• UTC is Standard: Remember that Spark performs timestamp calculations in UTC, which prevents ambiguity when dealing with data from multiple timezones.

Next Steps

In our next lesson, we will cover the CAST function, a general-purpose tool for converting data between various types. While we used specific functions like to_date() today, CAST provides a broader capability that you will frequently use to enforce the correct schema in your data transformations.