Date and Timestamp Functions

Spark tutorial · PySpark.in

PySpark Date and Timestamp Functions

Working with dates and times is common in data processing. PySpark provides built-in functions to get the current date/time, format and parse dates, compute date differences, and add/subtract days. Below we explore key functions with beginner-friendly explanations, followed by intermediate details and examples.

1. current_date() and current_timestamp()

Explanation:

current_date() returns today’s date (without time) as a column of type Date. For example, adding sf.current_date() to a DataFrame yields the system date when the query runs.
current_timestamp() returns the current date and time (timestamp) as a TimestampType column. For instance, using sf.current_timestamp() in a query gives you the exact date and time of query execution.
Use Case: These functions are often used to timestamp records (e.g., marking when data was ingested or a row was processed).

Example – adding current date/time to a DataFrame:

```

from pyspark.sql import SparkSession

from pyspark.sql import functions as sf

spark = SparkSession.builder.getOrCreate()

# Create a simple DataFrame with one row

df = spark.range(1).select(

sf.current_date().alias("today"),

sf.current_timestamp().alias("now")

)

df.show(truncate=False)

```

Here today has only the date (e.g., 2025-12-16) and now shows the full timestamp.

Explanation:

Both functions evaluate at query start and return a constant value throughout that query. In other words, multiple calls to current_timestamp() in the same query will return the same timestamp.
If you need the current date/time to change within a query (e.g. per row), you’d typically use Spark SQL’s now() or run separate queries. But for most batch processing tasks, a single timestamp or date for the entire job is desired.
These functions have no parameters. Internally, current_date() yields a DateType column, and current_timestamp() yields a TimestampType column.
Use Case: Apart from logging, current_timestamp() can be useful in streaming or incremental ETL jobs to record up-to-the-second processing times.

2. date_format() and to_date()

Beginner Explanation:

date_format(date, format) converts a date, timestamp, or string column into a formatted string column. The format is a date pattern like "yyyy-MM-dd" or "MM/dd/yyyy". For example, sf.date_format("date_col", "MM/dd/yyyy") turns a Date into a string like "12/31/2025".
to_date(col, format) parses a string (or timestamp) into a DateType column. You provide the input column and, optionally, the format of that column. For example, if you have "2025-12-31" as a string, sf.to_date("string_col", "yyyy-MM-dd") will convert it to a Date value 2025-12-31. If you omit the format, Spark tries to cast using the default format (usually "yyyy-MM-dd").

Example – formatting and parsing dates:

```

from pyspark.sql.types import StringType, StructType, StructField

from pyspark.sql import functions as sf

from pyspark.sql import SparkSession

spark = SparkSession.builder.getOrCreate()

# Sample data: string dates

data = [("2025-12-31",), ("2026-01-01",)]

schema = StructType([StructField("date_str", StringType(), True)])

df = spark.createDataFrame(data, schema)

# Format the string as a DateType column, then format back to a string

result = df.select(

sf.to_date("date_str", "yyyy-MM-dd").alias("parsed_date"),

sf.date_format("date_str", "MM/dd/yyyy").alias("formatted_str")

)

result.show()

```

The parsed_date column is of type Date (no time), and formatted_str shows the string in MM/dd/yyyy format.

Explanation:

Date Patterns: date_format uses Java datetime patterns (e.g., yyyy=year, MM=month, dd=day). You can use any combination like "dd MMMM yyyy". See Spark’s [datetime patterns] for all symbols. For example, date_format("date_col", "MMM dd, yyyy") might yield "Dec 16, 2025".
Parsing with to_date: When parsing strings with to_date, you must match the string’s format. For Spark 2.2 and later, to_date(col, format) accepts a format pattern. If the string doesn’t match, the result is null. For example, sf.to_date("12/31/2025", "MM-dd-yyyy") will return null because the delimiters differ; you’d need "MM/dd/yyyy".
By default (no format), to_date(col) casts the column to DateType (equivalent to .cast("date")). This means it expects ISO format (yyyy-MM-dd) by default.
Notes: Use date_format when you need a string for display or further string operations. Use to_date when converting data into proper DateType to do date arithmetic later. If you have a timestamp string and want to keep time, use to_timestamp() instead of to_date().

3. datediff() and months_between()

Explanation:

datediff(end, start) computes the number of days from start to end. The result is an integer (could be negative if end is before start). For example, the difference between "2025-01-01" and "2024-12-25" is 7 days.
months_between(date1, date2) returns the number of months between two dates as a floating-point number. If date1 is later than date2, the result is positive; if earlier, negative. If both dates have the same day-of-month (or are the last day of their months), the result is an integer; otherwise Spark assumes 31 days per month and may return a fractional value.
Use Case: These functions are useful for age calculations, billing cycles, or any analysis of intervals (e.g., days since account creation, months between contract start and end).

Example – calculating date differences:

```

from pyspark.sql import SparkSession

from pyspark.sql import functions as sf

spark = SparkSession.builder.getOrCreate()

data = [("2025-01-01", "2024-12-25"), ("2025-06-15", "2025-01-15")]

df = spark.createDataFrame(data, ["end_date", "start_date"])

df.select(

sf.datediff("end_date", "start_date").alias("days_diff"),

sf.months_between("end_date", "start_date").alias("months_diff")

).show(truncate=False)

```

Here, the first row shows 7 days difference (~0.21 months), and the second row is exactly 5 months apart (because June 15 vs Jan 15).

Explanation:

Signed vs. Absolute: Both functions return signed values. In the example above, swapping the inputs (e.g. datediff("2025-01-01", "2025-06-15")) would yield a negative number (-151).
Fractional Months: months_between’s fractional results come from assuming 31-day months for partial differences. For example, months_between("2025-01-15", "2024-12-25") yields roughly 0.6774 (because ~21 days ≈ 0.677 months). By default Spark rounds to 8 decimal places (which you can disable with roundOff=False).
Calendar Nuances: If one date is the last day of a month and the other is the last day of a shorter month, Spark handles it specially (both as whole months). E.g., months_between("2020-03-31", "2020-01-31") = 2.0.
Timestamp Inputs: These functions work on DateType or string columns. If you supply a timestamp, Spark casts it to date (truncating the time portion) before computation.
Real-World: Use datediff to measure elapsed days (e.g., days since purchase). Use months_between for things like pro-rating monthly subscriptions or calculating fractional years (age in months, etc.).

4. Adding and Subtracting Days

Explanation:

date_add(start, days) returns a new date that is days days after start. For example, adding 7 days to "2025-12-24" gives "2025-12-31".
date_sub(start, days) returns the date that is days days before start. For example, subtracting 10 days from "2025-01-10" gives "2024-12-31". These functions are useful for computing due dates, deadlines, or shifting windows of dates.

Example – shifting dates:

```

from pyspark.sql import SparkSession

from pyspark.sql import functions as sf

spark = SparkSession.builder.getOrCreate()

data = [("2025-12-24",), ("2025-01-10",)]

df = spark.createDataFrame(data, ["date"])

df.select(

sf.date_add("date", 7).alias("plus_7"),

sf.date_sub("date", 10).alias("minus_10")

).show()

```

The first row added 7 days to Dec 24, 2025; the second subtracted 10 days from Jan 10, 2025.

Explanation:

Negative Values: Both functions accept a negative days argument. For instance, date_add(start, -1) is equivalent to date_sub(start, 1). The documentation notes that a negative days in date_add will deduct days, and a negative days in date_sub will add days.
Edge Cases: These functions work correctly across month and year boundaries, handling leap years automatically. For example, adding 1 day to "2024-02-28" yields "2024-02-29" (leap year) or "2024-03-01" (non-leap).
Date vs. Timestamp: date_add and date_sub require the first argument to be a date column (or castable to date). If you provide a timestamp, it will be cast to date (dropping the time).
Aliases: Spark also provides dateadd (no underscore) as an alias of date_add, and similarly datediff is an alias of date_diff. But using the documented names is recommended.
Practical: Use these to build rolling windows (e.g., date_add(order_date, 30) to get a 30-day follow-up date), filter date ranges, or backfill data by subtracting days.

5. Converting String to Date

Explanation:

Converting a text field into a date allows you to do date calculations. The main function is to_date(string_col, format). You give it a column of type string (e.g. "12-31-2025") and a format pattern (e.g. "MM-dd-yyyy"). Spark then parses the string and returns a DateType column.
For example, sf.to_date("date_string", "MM-dd-yyyy") will convert "12-31-2025" to 2025-12-31 (as a date). If the string doesn’t match the format, Spark returns null.

Example – parsing strings to dates:

```

from pyspark.sql import SparkSession

from pyspark.sql import functions as sf

spark = SparkSession.builder.getOrCreate()

data = [("12-31-2025",), ("01-15-2026",), ("2026/02/01",)]

df = spark.createDataFrame(data, ["str_date"])

df.select(

sf.to_date("str_date", "MM-dd-yyyy").alias("parsed"),

sf.to_date("str_date", "yyyy/MM/dd").alias("parsed_alt")

).show()

```

This shows how different formats yield results:

The first two rows use the "MM-dd-yyyy" format and parse correctly. The third row only parses with the "yyyy/MM/dd" pattern. The other attempt yields null.

Explanation:

Default Casting: Calling to_date(col) without a format is equivalent to casting the column to date (expecting ISO format "yyyy-MM-dd"). For example, to_date("2025-12-31") yields a date, but to_date("31-12-2025") yields null by default.
Timestamp Strings: If your input string includes a time (e.g. "2025-12-31 23:59:59"), to_date will truncate the time and keep only the date. To preserve time, use to_timestamp.
Using unix_timestamp: In older Spark versions (pre-2.2), you might see workarounds using unix_timestamp and from_unixtime to parse strings. Nowadays, to_date with format is simpler.
Error Handling: A malformed date string will become null. It’s good practice to filter or validate after parsing. For example, using .filter(sf.to_date("str_date", "MM-dd-yyyy").isNotNull()) to keep only successfully parsed rows.
Real-World: Converting user-input dates, logs, or external data (often strings) into DateType is essential before doing any date arithmetic. Always verify the format and watch out for nulls after to_date().

Date and Timestamp Functions

PySpark Date and Timestamp Functions

1. current_date() and current_timestamp()

2. date_format() and to_date()

3. datediff() and months_between()

4. Adding and Subtracting Days

5. Converting String to Date

More Spark tutorials