DATE & TIMESTAMP
IN
Raushan Kumar
PYSPARK
IN DEPTH ANALYSIS
Raushan Kumar
[Link]
DATE AND TIME FUNCTION IN PYSPARK
In PySpark SQL, date functions are used to perform operations on
DateType or TimestampType columns, such as extracting parts of
the date, manipulating dates, and comparing dates.
Sample Dataframe used to demonstrate below functions
from [Link] import Row
from [Link] import *
# Sample data
data = [
Row(order_id=1, order_date="2024-02-18",
order_time="2024-02-18 12:30:00", unix_timestamp=1676726400),
Row(order_id=2, order_date="2023-11-23",
order_time="2023-11-23 09:15:00", unix_timestamp=1674080400),
Row(order_id=3, order_date="2022-05-06",
order_time="2022-05-06 17:45:00", unix_timestamp=1651854000)
]
# Create DataFrame
df = [Link](data)
# Show the DataFrame
[Link](truncate=False)
1
Here are some commonly used PySpark SQL date functions
1. current_date()
Returns the current date
from [Link] import current_date
df1=[Link]('Ingestion_Date',current_date())
[Link]()
Output
2. current_timestamp()
Returns the current timestamp (date + time)
from [Link] import current_timestamp
df1=[Link]('Ingestion_timestamp',current_timestamp())
[Link](truncate=False)
Output
2
3. date_add(start_date, num_days)
Adds a specified number of days to a date
from [Link] import date_add
df1=[Link]('new_order_date',date_add(col('order_date'),10))
[Link](truncate=False)
Output
4. date_sub(start_date, num_days)
Subtracts a specified number of days from a date.
from [Link] import date_sub
df1=[Link]('new_order_date',date_sub(col('order_date'),4))
[Link](truncate=False)
Output
3
5. datediff(end_date, start_date)
Returns the difference (in days) between two dates
from [Link] import datediff
df1=[Link]('new_order_date',date_sub(col('order_date'),4))
df2=[Link]('order_date_diff',datediff(col('order_date'),col('new_or
der_date')))
[Link](truncate=False)
Output
6. add_months(start_date, num_months)
Adds a specified number of months to a date
from [Link] import add_months
df1=[Link]('new_order_date',add_months(col('order_date'),4))
[Link](truncate=False)
Output
4
7. months_between(date1, date2)
Returns the number of months between two dates.
from [Link] import months_between
df1=[Link]('new_order_date',add_months(col('order_date'),4))
df2=[Link]('order_months_diff',months_between(col('new_order
_date'),col('order_date')))
[Link](truncate=False)
Output
8. year(date)
Extracts the year from a date or timestamp
from [Link] import year
df1=[Link]('order_year',year(col('order_date')))
[Link](truncate=False)
Output
5
9. month(date)
Extracts the month from a date or timestamp
from [Link] import month
df1=[Link]('order_month',month(col('order_time')))
[Link](truncate=False)
Output
10. dayofmonth(date)
Extracts the day of the month from a date
from [Link] import dayofmonth
df1=[Link]('order_day',dayofmonth(col('order_time')))
[Link](truncate=False)
Output
6
11. dayofweek(date)
Extracts the day of the week from a date (1 = Sunday, 7 = Saturday)
from [Link] import dayofweek
df1=[Link]('order_day_of_week',
when(dayofweek(col('order_date'))==1,'Sunday') \
.when(dayofweek(col('order_date'))==2,'Monday') \
.when(dayofweek(col('order_date'))==3,'Tuesday') \
.when(dayofweek(col('order_date'))==4,'Wednesday') \
.when(dayofweek(col('order_date'))==5,'Thursday') \
.when(dayofweek(col('order_date'))==6,'Friday') \
.when(dayofweek(col('order_date'))==7,'Saturday')
)
[Link](truncate=False)
Output
Example 2
df1=[Link]('day_of_week',dayofweek(col('order_time')))
[Link](truncate=False)
Output
7
12. hour(timestamp)
Extracts the hour of the day from a timestamp
from [Link] import hour
df1=[Link]('order_hour',hour(col('order_time')))
[Link](truncate=False)
Output
13. minute(timestamp)
Extracts the minute from a timestamp
from [Link] import minute
df1=[Link]('order_minute',minute(col('order_time')))
[Link](truncate=False)
Output
8
14. second(timestamp)
Extracts the second from a timestamp
from [Link] import second
df1=[Link]('order_second',second(col('order_time')))
[Link](truncate=False)
Output
15. to_date(string, format)
Converts a string to a date, using the specified format
from [Link] import to_date
df1=[Link]('order_date_string',lit('2025/02/19'))
[Link]()
[Link]()
9
Convert ‘order_date_string’ column from ‘string’ to ‘date’
df2=[Link]('order_date_str_date',to_date(col('order_date_string'),'
yyyy/MM/dd'))
[Link]()
[Link]()
Output
16. to_timestamp(string, format)
Converts a string to a timestamp, using the specified format
from [Link] import to_timestamp
df1=[Link]('order_timestamp',to_timestamp(col('order_time'),'yyyy
-MM-dd HH:mm:ss'))
[Link](truncate=False)
[Link]()
10
Output
17. from_unixtime(unix_time, format)
Converts Unix timestamp (seconds since epoch) to a string using the
given format
from [Link] import from_unixtime
df1=[Link]('unix_time',from_unixtime(col('unix_timestamp'),'yyyy-
MM-dd HH:mm:ss'))
[Link](truncate=False)
Output
11
18. unix_timestamp(date_string, format)
Converts a date string into Unix timestamp (seconds since epoch)
from [Link] import unix_timestamp
df1=[Link]('to_unix_time',unix_timestamp(col('order_date'),'yyyy-
MM-dd'))
[Link](truncate=False)
Output
19. last_day(date)
Returns the last day of the month for a given date
from [Link] import last_day
df1=[Link]('order_month_last_day',last_day(col('order_date')))
[Link](truncate=False)
Output
12
20. trunc(date, format)
Truncates a date to a specific unit of time (e.g., year, month, day)
from [Link] import trunc
df1=[Link]('first_day_order_month',trunc(col('order_date'),'MM'))
[Link](truncate=False)
Output
df1=[Link]('first_day_order_year',trunc(col('order_date'),'yyyy'))
[Link](truncate=False)
Output
13
Summary of PySpark SQL Date Functions
These date functions are very helpful for time series analysis,
data transformation, and reporting, especially when dealing with
time-based data!
By: Raushan Kumar
Please follow for more such content:
[Link]
14