HintsToday

A quick reference for date manipulation in PySpark:–

Now starts our Post:-

PySpark date functions along with detailed explanations and examples on Dataframes

Here’s a comprehensive list of some common PySpark date functions along with detailed explanations and examples on Dataframes(We will again discuss these basis Pyspark sql Queries):

1. current_date()

Returns the current date.

from pyspark.sql.functions import current_date

df = spark.createDataFrame([(1,)], ["dummy"])
df.select(current_date().alias("current_date")).show()

2. current_timestamp()

Returns the current timestamp.

from pyspark.sql.functions import current_timestamp

df.select(current_timestamp().alias("current_timestamp")).show()

3. date_format()

Formats a date using the specified format.

from pyspark.sql.functions import date_format

df = spark.createDataFrame([("2010-02-05",)], ["date"])
df.select(date_format("date", "MM/dd/yyyy").alias("formatted_date")).show()

4. year(), month(), dayofmonth()

Extracts the year, month, and day from a date.

from pyspark.sql.functions import year, month, dayofmonth

df.select(year("date").alias("year"),
          month("date").alias("month"),
          dayofmonth("date").alias("day")).show()

5. date_add(), date_sub()

Adds or subtracts a specified number of days to/from a date.

from pyspark.sql.functions import date_add, date_sub
df.select(date_add("date", 10).alias("date_add"),date_sub("date", 10).alias("date_sub")).show()

6. datediff()

Returns the difference in days between two dates.

from pyspark.sql.functions import datediff

df2 = spark.createDataFrame([("2010-02-15",)], ["date2"])
df.join(df2).select(datediff("date2", "date").alias("datediff")).show()

7. add_months()

Adds a specified number of months to a date.

from pyspark.sql.functions import add_months

df.select(add_months("date", 1).alias("add_months")).show()

8. months_between()

Returns the number of months between two dates.

from pyspark.sql.functions import months_between

df.join(df2).select(months_between("date2", "date").alias("months_between")).show()

9. next_day()

Returns the first date which is the specified day of the week after the given date.

from pyspark.sql.functions import next_day

df.select(next_day("date", "Sunday").alias("next_day")).show()

10. last_day()

Returns the last day of the month which the given date belongs to.

from pyspark.sql.functions import last_day

df.select(last_day("date").alias("last_day")).show()

11. trunc()

Truncates a date to the specified unit (‘year’, ‘month’).

from pyspark.sql.functions import trunc

df.select(trunc("date", "MM").alias("trunc_month"),
          trunc("date", "YY").alias("trunc_year")).show()

12. date_trunc()

Truncates a timestamp to the specified unit (‘year’, ‘month’, ‘day’, ‘hour’, ‘minute’).

from pyspark.sql.functions import date_trunc

df = spark.createDataFrame([("2010-02-05 12:34:56",)], ["timestamp"])
df.select(date_trunc("hour", "timestamp").alias("date_trunc_hour")).show()

13. from_unixtime()

Converts the number of seconds from the Unix epoch to a string representing the timestamp.

from pyspark.sql.functions import from_unixtime

df = spark.createDataFrame([(1234567890,)], ["unix_time"])
df.select(from_unixtime("unix_time").alias("timestamp")).show()

14. unix_timestamp()

Converts a string timestamp to the number of seconds from the Unix epoch.

from pyspark.sql.functions import unix_timestamp

df = spark.createDataFrame([("2010-02-05 12:34:56",)], ["timestamp"])
df.select(unix_timestamp("timestamp").alias("unix_timestamp")).show()

15. to_date()

Converts a string to a date.

from pyspark.sql.functions import to_date

df = spark.createDataFrame([("2010-02-05",)], ["date_str"])
df.select(to_date("date_str").alias("date")).show()

16. to_timestamp()

Converts a string to a timestamp.

from pyspark.sql.functions import to_timestamp

df = spark.createDataFrame([("2010-02-05 12:34:56",)], ["timestamp_str"])
df.select(to_timestamp("timestamp_str").alias("timestamp")).show()

17. weekofyear()

Returns the week of the year for a date.

from pyspark.sql.functions import weekofyear

df = spark.createDataFrame([("2010-02-05",)], ["date"])
df.select(weekofyear("date").alias("weekofyear")).show()

18. quarter()

Returns the quarter of the year for a date.

from pyspark.sql.functions import quarter

df.select(quarter("date").alias("quarter")).show()

19. dayofweek()

Returns the day of the week for a date.

from pyspark.sql.functions import dayofweek

df.select(dayofweek("date").alias("dayofweek")).show()

Example Project: Combining Multiple Date Functions

from pyspark.sql import SparkSession
from pyspark.sql.functions import col, date_format, year, month, dayofmonth, date_add, date_sub, datediff, add_months, months_between, next_day, last_day, trunc, date_trunc, from_unixtime, unix_timestamp, to_date, to_timestamp, weekofyear, quarter, dayofweek

# Initialize Spark session
spark = SparkSession.builder.appName("date_functions_example").getOrCreate()

# Create a DataFrame with a sample date
data = [("2010-02-05", "2010-02-15 12:34:56", 1234567890)]
columns = ["date", "timestamp", "unix_time"]
df = spark.createDataFrame(data, columns)

# Applying various date functions
result = df.select(
    col("date"),
    col("timestamp"),
    col("unix_time"),
    date_format("date", "MM/dd/yyyy").alias("formatted_date"),
    year("date").alias("year"),
    month("date").alias("month"),
    dayofmonth("date").alias("day"),
    date_add("date", 10).alias("date_add"),
    date_sub("date", 10).alias("date_sub"),
    datediff("timestamp", "date").alias("datediff"),
    add_months("date", 1).alias("add_months"),
    months_between("timestamp", "date").alias("months_between"),
    next_day("date", "Sunday").alias("next_day"),
    last_day("date").alias("last_day"),
    trunc("date", "MM").alias("trunc_month"),
    trunc("date", "YY").alias("trunc_year"),
    date_trunc("hour", "timestamp").alias("date_trunc_hour"),
    from_unixtime("unix_time").alias("from_unixtime"),
    unix_timestamp("timestamp").alias("unix_timestamp"),
    to_date("timestamp").alias("to_date"),
    to_timestamp("timestamp").alias("to_timestamp"),
    weekofyear("date").alias("weekofyear"),
    quarter("date").alias("quarter"),
    dayofweek("date").alias("dayofweek")
)

# Show the results
result.show(truncate=False)

PySpark date functions along with detailed explanations and examples on Spark SQL

In PySpark, you can use various built-in date functions directly within SQL queries to manipulate and extract data from timestamp, date, and string columns. PySpark’s SQL API provides a wide range of date functions similar to SQL, which allows you to perform operations like formatting, adding/subtracting time intervals, and extracting specific components from dates.

Here are some of the most commonly used PySpark date functions in SQL queries:

1. Current Date and Time Functions

• current_date() – Returns the current date.
• current_timestamp() – Returns the current timestamp (date and time).
• now() – Alias for current_timestamp().

Example:

SELECT current_date() AS today_date,
       current_timestamp() AS current_time
FROM your_table

2. Date and Time Arithmetic Functions

• date_add(date, num_days) – Adds num_days days to the given date.
• date_sub(date, num_days) – Subtracts num_days days from the given date.
• add_months(date, num_months) – Adds num_months months to the given date.
• datediff(end_date, start_date) – Returns the difference between two dates.
• months_between(end_date, start_date) – Returns the number of months between two dates.

Example:

SELECT date_add(current_date(), 5) AS five_days_from_now,
       date_sub(current_date(), 10) AS ten_days_ago,
       add_months(current_date(), 3) AS three_months_later,
       datediff('2024-12-31', '2024-01-01') AS days_diff
FROM your_table

3. Date Formatting and Parsing Functions

• date_format(date, format) – Formats a date according to the given format string.
• to_date(string) – Converts a string to a date.
• to_timestamp(string) – Converts a string to a timestamp.
• unix_timestamp() – Converts a date or timestamp to the number of seconds since the Unix epoch (1970-01-01).
• from_unixtime(unix_time, format) – Converts Unix time to a string in the specified format.

Example:

SELECT date_format(current_timestamp(), 'yyyy-MM-dd') AS formatted_date,
       to_date('2024-09-01', 'yyyy-MM-dd') AS converted_date,
       unix_timestamp('2024-09-01', 'yyyy-MM-dd') AS unix_time,
       from_unixtime(1693564800, 'yyyy-MM-dd') AS from_unix_time
FROM your_table

4. Extracting Components from Dates

• year(date) – Extracts the year from a date.
• month(date) – Extracts the month from a date.
• day(date) or dayofmonth(date) – Extracts the day of the month from a date.
• hour(timestamp) – Extracts the hour from a timestamp.
• minute(timestamp) – Extracts the minute from a timestamp.
• second(timestamp) – Extracts the second from a timestamp.
• dayofweek(date) – Returns the day of the week (1 = Sunday, 7 = Saturday).
• weekofyear(date) – Returns the week of the year for a given date.

Example:

SELECT year(current_date()) AS year,
       month(current_date()) AS month,
       day(current_date()) AS day,
       dayofweek(current_date()) AS day_of_week,
       weekofyear(current_date()) AS week_of_year
FROM your_table

5. Date Truncation Functions

• trunc(date, format) – Truncates a date to the specified unit (year, month, etc.).
• date_trunc(format, timestamp) – Truncates a timestamp to the specified unit.

Example:

SELECT trunc(current_date(), 'MM') AS truncated_to_month,
       date_trunc('MM', current_timestamp()) AS timestamp_truncated_to_month
FROM your_table

6.EXTRACT Function in PySpark SQL:-

In PySpark SQL (or standard SQL), extract() is a built-in function for extracting parts of a date or timestamp, such as extracting the year, month, day, hour, minute, etc., from a date or timestamp column.

Syntax:

EXTRACT(field FROM source)

• field: Specifies the part of the date/time to extract (e.g., YEAR, MONTH, DAY, HOUR, MINUTE, etc.).
• source: The column containing a date or timestamp.

Example Usage of extract() in PySpark SQL Queries:

If you have a PySpark DataFrame with a date column and you want to extract specific components (such as year or month), you can use extract() in a SQL query.

Example 1: Extracting Year, Month, and Day from a Date:

Assume you have a PySpark DataFrame with a date column in YYYY-MM-DD format, and you want to extract the year, month, and day.

from pyspark.sql import SparkSession
# Create a SparkSession
spark = SparkSession.builder.appName("ExtractExample").getOrCreate()
# Sample data
data = [("2023-09-01",), ("2024-12-31",), ("2020-01-20",)]
df = spark.createDataFrame(data, ["DateColumn"])
# Create a temporary SQL table
df.createOrReplaceTempView("date_table")
# SQL query to extract year, month, and day
result = spark.sql("""
    SELECT
        DateColumn,
        EXTRACT(YEAR FROM DateColumn) AS Year,
        EXTRACT(MONTH FROM DateColumn) AS Month,
        EXTRACT(DAY FROM DateColumn) AS Day
    FROM date_table
""")
result.show()

Output:

+----------+----+-----+---+
|DateColumn|Year|Month|Day|
+----------+----+-----+---+
|2023-09-01|2023|    9|  1|
|2024-12-31|2024|   12| 31|
|2020-01-20|2020|    1| 20|
+----------+----+-----+---+

Example 2: Extracting Hour and Minute from a Timestamp:

If you have a timestamp column and you want to extract specific time components such as hour and minute, you can use EXTRACT() in a SQL query.

data = [("2023-09-01 12:30:45",), ("2024-12-31 18:45:00",), ("2020-01-20 07:15:25",)]
df = spark.createDataFrame(data, ["TimestampColumn"])
# Create a temporary SQL table
df.createOrReplaceTempView("timestamp_table")
# SQL query to extract hour and minute from the timestamp
result = spark.sql("""
    SELECT
        TimestampColumn,
        EXTRACT(HOUR FROM TimestampColumn) AS Hour,
        EXTRACT(MINUTE FROM TimestampColumn) AS Minute
    FROM timestamp_table
""")
result.show()

Output:

+-------------------+----+------+
|   TimestampColumn |Hour|Minute|
+-------------------+----+------+
|2023-09-01 12:30:45|  12|    30|
|2024-12-31 18:45:00|  18|    45|
|2020-01-20 07:15:25|   7|    15|
+-------------------+----+------+

Fields Supported by EXTRACT():

You can extract the following parts from a date or timestamp:

• YEAR: Extracts the year.
• MONTH: Extracts the month.
• DAY: Extracts the day.
• HOUR: Extracts the hour from a timestamp.
• MINUTE: Extracts the minute from a timestamp.
• SECOND: Extracts the second from a timestamp.

Using EXTRACT() in PySpark’s SQL Queries:

In PySpark, you typically define a temporary view using createOrReplaceTempView() to run SQL queries with the EXTRACT() function.

Example Scenario:

If you have a dataset with timestamps and you want to run a SQL query to extract the hour and minute, you can write a PySpark SQL query like:

df.createOrReplaceTempView("log_table")
spark.sql("""
    SELECT
        EXTRACT(HOUR FROM timestamp_column) AS Hour,
        EXTRACT(MINUTE FROM timestamp_column) AS Minute
    FROM log_table
""").show()

This query will return the hour and minute extracted from the timestamp column.

• The EXTRACT() function in Spark SQL allows you to extract specific parts (like year, month, day, hour, minute) from date or timestamp columns.
• It is used in SQL-based queries within PySpark, similar to how you’d use SQL functions for querying structured data.

So, if you saw extract() in a PySpark SQL query, it was likely extracting parts of a date or timestamp column.

7. Other Useful Date Functions

• last_day(date) – Returns the last day of the month for a given date.
• next_day(date, day_of_week) – Returns the next date after the given date for the specified day of the week.
• quarter(date) – Returns the quarter of the year (1, 2, 3, or 4).

Example:

SELECT last_day(current_date()) AS last_day_of_month,
       next_day(current_date(), 'Monday') AS next_monday,
       quarter(current_date()) AS current_quarter
FROM your_table

Complete Example of a PySpark SQL Query:

Assume you have a table events with a timestamp column named event_time. Here’s a query that selects events from the current year, formats the event date, and extracts various components from the timestamp:

SELECT event_time,
       date_format(event_time, 'yyyy-MM-dd') AS formatted_date,
       year(event_time) AS event_year,
       month(event_time) AS event_month,
       dayofmonth(event_time) AS event_day,
       hour(event_time) AS event_hour,
       minute(event_time) AS event_minute,
       second(event_time) AS event_second,
       quarter(event_time) AS event_quarter,
       weekofyear(event_time) AS event_week
FROM events
WHERE year(event_time) = year(current_date())

Notes:

Integration in PySpark: You can execute SQL queries with date functions using spark.sql().

For example:

result = spark.sql(""" SELECT date_add(current_date(), 5) AS five_days_from_now, year(current_timestamp()) AS current_year FROM your_table """) result.show()

Date Functions for Filtering: You can use these functions in the WHERE clause to filter data based on dates:

SELECT * FROM your_table WHERE event_time >= '2023-01-01' AND event_time <= '2023-12-31'

These built-in PySpark SQL date functions provide extensive capabilities for handling, formatting, and manipulating date and time data at scale in Spark applications.

Python date functionality vs Pyspark date functionality

Python and PySpark both provide extensive date and time manipulation functionalities, but they serve different use cases and are part of distinct ecosystems. Here’s a comparison of Python date functionality (using the standard datetime module) and PySpark date functionality (using PySpark SQL functions like date_add, date_format, etc.).

1. Python Date Functionality

Python’s standard datetime module provides a comprehensive suite of tools for handling dates and times. It is widely used in standalone Python scripts, applications, and non-distributed environments.

Key Functions and Methods (Python’s datetime):

from datetime import datetime, timedelta, date
# Get current date and time
current_datetime = datetime.now()
# Get current date
current_date = date.today()
# Add or subtract days
future_date = current_date + timedelta(days=30)
past_date = current_date - timedelta(days=30)
# Format date to a string (custom format)
formatted_date = current_date.strftime('%Y%m%d')
# Parse a string to a date
parsed_date = datetime.strptime('2024-09-10', '%Y-%m-%d')
# Difference between two dates
date_diff = future_date - past_date

Key Features:

• datetime.now(): Gets the current date and time.
• timedelta(): Adds or subtracts a specific time period (days, weeks, seconds, etc.) to/from a datetime object.
• strftime(): Formats a datetime object into a string (customizable formatting).
• strptime(): Converts a string into a datetime object based on a specified format.
• Arithmetic: You can perform arithmetic between two datetime objects (e.g., subtracting dates).

Use Cases:

• Suitable for local or single-machine date/time manipulations.
• Great for date/time parsing and formatting when working with smaller datasets.
• Commonly used for non-distributed data processing.

2. PySpark Date Functionality

PySpark provides SQL-based date/time manipulation functions, optimized for distributed processing across large datasets. These are essential for working with big data environments and are used inside DataFrame queries.

Key PySpark Date Functions:

from pyspark.sql.functions import current_date, date_add, date_sub, date_format, to_date, col
# Get current date
df = spark.createDataFrame([(1,)], ["id"]).withColumn("current_date", current_date())
# Add or subtract days
df = df.withColumn("future_date", date_add(col("current_date"), 30))
df = df.withColumn("past_date", date_sub(col("current_date"), 30))
# Format date to a string (yyyyMM format)
df = df.withColumn("formatted_date", date_format(col("current_date"), 'yyyyMM'))
# Parse a string to date (convert '2024-09-10' to a date type)
df = df.withColumn("parsed_date", to_date(lit("2024-09-10"), 'yyyy-MM-dd'))
# Show results
df.show()

Key Features:

• current_date(): Returns the current date (no time part).
• date_add() and date_sub(): Adds or subtracts days from a date column.
• date_format(): Formats a date column into a string (customizable like yyyyMM or yyyyMMdd).
• to_date(): Converts a string into a date object within a DataFrame.
• Date Arithmetic: You can perform arithmetic with dates directly within PySpark DataFrames.

Additional PySpark Date Functions:

• months_between(): Calculates the difference between two dates in months.
• year(), month(), dayofmonth(): Extract year, month, or day from a date column.
• datediff(): Computes the difference between two dates in days.

Use Cases:

• Suitable for distributed data processing (i.e., processing large datasets using clusters).
• Can handle complex date manipulations directly within SQL-like DataFrame queries.
• Ideal for big data workflows where data is stored and processed in distributed databases like Hive, HDFS, or cloud environments.

Key Differences Between Python and PySpark Date Functionalities:

Summary:

• Python datetime: Best for small-scale, single-machine data processing and parsing tasks where date formatting or arithmetic is required.
• PySpark pyspark.sql.functions: Ideal for large-scale, distributed data processing environments where dates need to be handled inside DataFrames in a scalable way.

If your use case involves big data or distributed data processing, PySpark’s date functions are more suited to the task. For local, lightweight date manipulations, Python’s datetime module is more appropriate.

Pages: 1 2 3