Skip to main content


Date and Timestamp in Spark SQL

Date and time

Spark SQL offers a set of built-in standard functions for handling dates and timestamps within the DataFrame API. These functions are valuable for performing operations involving date and time data. They accept inputs in various formats, including Date type, Timestamp type, or String. If the input is provided as a String, it must be in a format compatible with date (e.g., yyyy-MM-dd) or timestamp (e.g., yyyy-MM-dd HH:mm:ss.SSSS) representations. The functions return the corresponding date or timestamp values based on the input type. If the input string cannot be successfully converted to a date or timestamp, the functions return null.

Let’s see some Date and Timestamp syntax and examples in Spark SQL:

First Create a Sample Dataset and save it as View, which we can use for seeing the date and timestamp functions in SQL.

To Know how to create dataframe and it methods , check into :

and also to know about writing into Table , look into :

  • Creating a temp view
val df = spark.createDataFrame(Seq(
  ("2024-02-27", "2024-02-27 15:30:45.123"),
  ("2024-01-15", "2024-01-15 08:45:30.555"),
  ("2023-11-20", "2023-11-20 12:00:00.000"),
  ("invalid date", "invalid timestamp")
)).toDF("date_string", "timestamp_string")
  • Displaying my_table

My Table

Date Data Type

The DateType represents a date without a time component. Dates can be created from string using the DATE literal or the TO_DATE() function.

  • Example of DATE Literal

Date Literal

  • Example of TO_DATE() function

To Date Function

Timestamp Data Type

The TimestampType represents a date and time with millisecond precision. Timestamps can be created using the TIMESTAMP literal or the TO_TIMESTAMP() function.

  • Example of TIMESTAMP Literal

Timestamp Literal

  • Example of TO_TIMESTAMP() function

To Timestamp Function

Date and Timestamp Functions

Spark SQL provides various functions for working with dates and timestamps:

  • Date Functions: year(), month(), dayofmonth(), dayofweek(), dayofyear(), weekofyear(), etc.

Year Month Day

  • Timestamp Functions: hour(), minute(), second(), date_format(), etc.

Hour Minute Second

Formatting Dates and Timestamps

You can format dates and timestamps using the date_format() function.

  • date_format example with formating Date

Date Format

  • date_format example with formating timestamp

Date Format Timestamp

Date and Timestamp Arithmetic

You can perform arithmetic operations on dates and timestamps using functions like date_add(), date_sub(), and arithmetic operators.

  • Example of adding 1 day to given dates

Date Add

  • Example of adding 1 hour to given timestamp

Timestamp Add

Filtering Dates and Timestamps

You can filter data based on dates and timestamps using comparison operators.

  • Example of filtering with date

Date Filter

  • Example of filtering with timestamp

Timestamp Filter

These are some common operations you can perform with dates and timestamps in Spark SQL. They are essential for various analytical tasks, especially when dealing with time-series data.


Spqk SqL build-in function :

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Aarthii Gurunathan

Aarthii is an Associate Technical Consultant at Perficient, currently specializes as a Databricks Spark Developer. She is proficient in technologies such as SQL, Databricks, Spark, Scala and Java. She enthusiastically explores new technologies, continually learning to maintain her productivity. She endeavours in contributing in various capacities to give back to the community.

More from this Author

Follow Us