Date functions

Spark tutorial · PySpark.in

Date and Time Functions: current_date, date_add, datediff, to_date, year, month

Overview: Dates and times are critical in many datasets (think transaction dates, sign-up dates, etc.). PySpark offers numerous functions to handle dates and times. Here we focus on common date functions:

current_date() – generates a column for the current date (date when the Spark job runs).
date_add(date_col, days) – adds a number of days to a date (or subtracts if the number is negative).
datediff(end, start) – calculates the number of days between two dates.
to_date(string_col, format) – converts a string to a Spark DateType given a format.
year(date_col) and month(date_col) – extract the year and month as integers from a date.

These functions allow you to manipulate dates, such as computing future or past dates, measuring intervals, and extracting parts of dates for grouping or filtering.

Typical Use Cases:

Adding or subtracting days from a given date (e.g., calculate a due date 30 days after an invoice date).
Calculating the difference in days between two dates (e.g., number of days a ticket was open, or how many days since a user last logged in).
Converting textual date representations into actual date objects for proper comparisons and computations.
Extracting year or month (or day) to do time-based grouping (like number of signups per year, sales per month).
Getting the current date or timestamp to timestamp records or filter records relative to "today".

Let's use an example of an employee hiring DataFrame to demonstrate these functions. We'll have a string column for hire date (as is common when data is read from CSV) which we'll convert to a date, and then use date functions.

Here hire_date_str is a string in format "yyyy-MM-dd". We will convert that to an actual date, then demonstrate date functions.

Converting String to Date with to_date

The to_date(col, format) function takes a string and a format and returns a DateType column. If the format is omitted, it tries to infer or uses the default 'yyyy-MM-dd' for casting. It's safer to provide the format if your string is in a known format.

Example: Convert hire_date_str to a date, and add today's date.

```

from pyspark.sql.functions import to_date, current_date,col

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("BuiltInFuncDemo").getOrCreate()

hire_data = [

("Alice", "2022-01-10"),

("Bob", "2023-07-01"),

("Charlie", "2020-11-23"),

("Diana", "2023-02-20")

]

df_hire = spark.createDataFrame(hire_data, ["name", "hire_date_str"])

df_hire_dates = df_hire.withColumn("hire_date", to_date(col("hire_date_str"), "yyyy-MM-dd")) \

.withColumn("today", current_date())

df_hire_dates.select("name", "hire_date_str", "hire_date", "today").show()

```

We now have a proper hire_date (of type Date) and a today column (which is the current date when this was executed, for example it shows 2025-12-09 here). All calls to current_date() in the same query produce the same value, so every row has the same today.

Having hire_date as a DateType allows date arithmetic. If we kept it as string, Spark wouldn't know how to subtract or add days correctly.

Notice that to_date with the format "yyyy-MM-dd" worked directly on our string format. If the string was in a different format (say "01/10/2022"), we'd need to adjust the format accordingly (e.g., to_date(col, "MM/dd/yyyy")). If parsing fails or the string is not a valid date, to_date will result in NULL for that row.

Date Arithmetic with date_add and datediff

Now that we have date columns, we can perform arithmetic:

date_add(start_date, days) gives you a date some number of days after the start_date. Use a negative number to go backwards.
datediff(end_date, start_date) gives the number of days between the two dates (essentially end_date - start_date in days).

Example: Calculate a 90-day probation end date for each hire, and how many days have passed since hire.

```

from pyspark.sql.functions import date_add, datediff,col

from pyspark.sql.functions import to_date, current_date

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("BuiltInFuncDemo").getOrCreate()

hire_data = [

("Alice", "2022-01-10"),

("Bob", "2023-07-01"),

("Charlie", "2020-11-23"),

("Diana", "2023-02-20")

]

df_hire_calc = df_hire_dates.withColumn("probation_end", date_add(col("hire_date"), 90)) \

.withColumn("days_since_hire", datediff(col("today"), col("hire_date")))

df_hire_calc.select("name", "hire_date", "probation_end", "days_since_hire").show()

```

Interpretation:

Alice was hired 2022-01-10, so 90 days later is 2022-04-10 (probation_end). As of today (2025-12-09 in this example), days_since_hire is 1064 days. That’s roughly 2 years and 11 months.
Bob, hired 2023-07-01, probation_end 2023-09-29, and 527 days since hire.
Charlie, hired late 2020, has the largest days_since_hire (1843 days).
Diana, hired 2023-02-20, probation ended 2023-05-21, and 659 days since hire.

This shows date_add simply added 90 days (we could also subtract by using -90 if needed). The datediff(today, hire_date) gives how many days from hire to today. If we flipped it (hire_date, today) it would give negative values since hire_date is earlier; order matters.

Note: There are similar functions like date_sub (which is essentially date_add with negative days) and for timestamps there is add_months or months_between etc., but we stick to days here.

Extracting Year and Month

Often you want to group or filter by parts of dates. PySpark provides year(col) and month(col) to get the year and month as integers.

Example: Extract year and month of hire.

```

from pyspark.sql.functions import year, month

from pyspark.sql.functions import to_date, current_date,col

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("BuiltInFuncDemo").getOrCreate()

hire_data = [

("Alice", "2022-01-10"),

("Bob", "2023-07-01"),

("Charlie", "2020-11-23"),

("Diana", "2023-02-20")

]

df_hire_calc.select("name", "hire_date", year(col("hire_date")).alias("hire_year"), month(col("hire_date")).alias("hire_month")).show()

```

We can see:

Alice hired in year 2022, month 1 (January).
Bob in 2023, month 7 (July).
Charlie in 2020, month 11 (November).
Diana in 2023, month 2 (February).

With these, you could, for example, group by hire_year to see hires per year, or filter where hire_month == 12 to find who was hired in December, etc.

Best Practices and Tips for Date Functions:

Always convert strings to date (or timestamp) types before doing date operations. As seen, datediff and date_add work on Date/Timestamp types, not on strings.
current_date() is useful for stamping when a job was run or filtering relative to today. It returns a DateType of the date when the Spark job executes (the start of the query evaluation). Similarly, current_timestamp() gives a timestamp (date and time).
If working with timestamps and you care about time components (hours/minutes), you'll use current_timestamp and functions like datediff (still counts days ignoring time) or unix_timestamp etc., but for simplicity we focused on dates.
date_add is straightforward for day arithmetic. For adding months, use add_months(date, n); for difference in months, months_between(end, start) can give fractional months.
datediff(end, start) gives pure difference in days. If you only had one date and needed to see how many days from that date to today, combining with current_date() as we did is a common pattern.
Year, month (and also dayofmonth, dayofweek, etc.) help in extracting parts of dates. This is very handy for creating features or grouping data. For example, year(birthdate) to group people by birth year.
Be mindful of time zone if your data has timestamps. The functions above, when using pure DateType, are date-only and not time zone sensitive. But if you have a TimestampType and use year(), Spark will take the year in the session’s time zone. In many data engineering tasks, using DateType for dates avoids a lot of confusion (time zone issues usually come with Timestamp and when shifting to/from strings).
When parsing dates with to_date (or to_timestamp for datetime), ensure your format matches the input string exactly, otherwise you'll get nulls. The example format "yyyy-MM-dd" is a common one. If your data was "01-Jan-2022", the format would be "dd-MMM-yyyy", etc.
After converting to date, you can use date_format(date, "pattern") to format it back to a string if needed for output (for example, to get "Jan 2022" as a string, you could use date_format(hire_date, "MMM yyyy")).

Working with dates correctly is crucial for analyses like retention (days since last login), cohorting (grouping by signup month/year), and so on. Spark's date functions help ensure these calculations are done correctly and efficiently on distributed data.

Date functions

Date and Time Functions: current_date, date_add, datediff, to_date, year, month

More Spark tutorials