Introduction
In this lesson, we will explore the various date and timestamp functions available in Spark SQL using Java. Handling dates is a critical aspect of data engineering as it allows us to perform time-based analysis and aggregations in our datasets. Knowing the appropriate functions to use and when to use them can streamline data manipulations and enhance the efficiency of your Spark applications.
Importance of Date Functions
Date functions are essential because they help manipulate and analyze time-related data across various scenarios, such as data cleansing, time-series analysis, and event tracking. They also ensure that date comparisons and calculations are accurate, which is critical in any analytical task, especially when forecasting trends or identifying temporal patterns.
Most Used Date Functions
Below is a list of some of the most used date functions in Spark SQL:
- current_date(): Returns the current date.
- current_timestamp(): Returns the current timestamp.
- date_format(date, format): Converts a date to a specific format.
- to_date(string, format): Converts a string to a date.
- datediff(endDate, startDate): Returns the difference in days between two dates.
- add_days(startDate, days): Adds a specified number of days to a date.
- sub_days(startDate, days): Subtracts a specified number of days from a date.
- date_add(startDate, days): Adds a specified number of days to a date.
- date_sub(startDate, days): Subtracts a specified number of days from a date.
When to Use Date Functions
These functions are employed in a variety of scenarios:
- Data enrichment: When there's a need to create additional temporal fields, such as extracting year, month, or day.
- Data filtering: Using date conditions to select subsets of data from time windows.
- Calculating durations: When it’s necessary to find time intervals between records, such as analyzing the length of events.
Spark Date Functions Examples
Below are the most used examples of Date Functions converted into Java Spark.
current_date() and date_format()
We will see how to get the current date and convert a date into a specific date format using date_format() with a Java example. The example below parses the date and converts it from ‘yyyy-dd-mm’ to ‘MM-dd-yyyy’ format.
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;
import static org.apache.spark.sql.functions.*;
public class DateFunctionsExample {
public static void main(String[] args) {
SparkSession spark = SparkSession.builder()
.appName("Date Functions Example")
.master("local[*]")
.getOrCreate();
Dataset<Row> df = spark.createDataFrame(Arrays.asList(
RowFactory.create("2019-01-23")
), DataTypes.createStructType(new StructField[]{
DataTypes.createStructField("Input", DataTypes.StringType, false)
}));
df.select(
current_date().alias("current_date"),
col("Input"),
date_format(col("Input"), "MM-dd-yyyy").alias("format")
).show();
}
}
to_date()
The below example converts the string in date format ‘MM/dd/yyyy’ to a DateType ‘yyyy-MM-dd’ using to_date().
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;
import static org.apache.spark.sql.functions.*;
public class DateToStringExample {
public static void main(String[] args) {
SparkSession spark = SparkSession.builder()
.appName("Date to String Example")
.master("local[*]")
.getOrCreate();
Dataset<Row> df = spark.createDataFrame(Arrays.asList(
RowFactory.create("04/13/2019")
), DataTypes.createStructType(new StructField[]{
DataTypes.createStructField("Input", DataTypes.StringType, false)
}));
df.select(
col("Input"),
to_date(col("Input"), "MM/dd/yyyy").alias("to_date")
).show();
}
}
datediff()
The below example returns the difference between two dates using datediff() with a Java example.
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;
import static org.apache.spark.sql.functions.*;
public class DateDiffExample {
public static void main(String[] args) {
SparkSession spark = SparkSession.builder()
.appName("DateDiff Example")
.master("local[*]")
.getOrCreate();
Dataset<Row> df = spark.createDataFrame(Arrays.asList(
RowFactory.create("2019-01-23"),
RowFactory.create("2019-06-24"),
RowFactory.create("2019-09-20")
), DataTypes.createStructType(new StructField[]{
DataTypes.createStructField("input", DataTypes.StringType, false)
}));
df.select(
col("input"),
current_date(),
datediff(current_date(), col("input")).alias("diff")
).show();
}
}
months_between()
Below example returns the months between two dates using months_between() with Java.
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;
import static org.apache.spark.sql.functions.*;
public class MonthsBetweenExample {
public static void main(String[] args) {
SparkSession spark = SparkSession.builder()
.appName("Months Between Example")
.master("local[*]")
.getOrCreate();
Dataset<Row> df = spark.createDataFrame(Arrays.asList(
RowFactory.create("2019-01-23"),
RowFactory.create("2019-06-24"),
RowFactory.create("2019-09-20")
), DataTypes.createStructType(new StructField[]{
DataTypes.createStructField("date", DataTypes.StringType, false)
}));
df.select(
col("date"),
current_date(),
datediff(current_date(), col("date")).alias("datediff"),
months_between(current_date(), col("date")).alias("months_between")
).show();
}
}
trunc()
The below example truncates the date at a specified unit using trunc() with Java.
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;
import static org.apache.spark.sql.functions.*;
public class TruncDateExample {
public static void main(String[] args) {
SparkSession spark = SparkSession.builder()
.appName("Trunc Date Example")
.master("local[*]")
.getOrCreate();
Dataset<Row> df = spark.createDataFrame(Arrays.asList(
RowFactory.create("2019-01-23"),
RowFactory.create("2019-06-24"),
RowFactory.create("2019-09-20")
), DataTypes.createStructType(new StructField[]{
DataTypes.createStructField("input", DataTypes.StringType, false)
}));
df.select(
col("input"),
trunc(col("input"), "Month").alias("Month_Trunc"),
trunc(col("input"), "Year").alias("Month_Year"),
trunc(col("input"), "Month").alias("Month_Trunc")
).show();
}
}
add_months(), date_add(), date_sub()
Here, I am adding and subtracting the date and month from a given input.
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;
import static org.apache.spark.sql.functions.*;
public class AddSubtractDateExample {
public static void main(String[] args) {
SparkSession spark = SparkSession.builder()
.appName("Add/Subtract Date Example")
.master("local[*]")
.getOrCreate();
Dataset<Row> df = spark.createDataFrame(Arrays.asList(
RowFactory.create("2019-01-23"),
RowFactory.create("2019-06-24"),
RowFactory.create("2019-09-20")
), DataTypes.createStructType(new StructField[]{
DataTypes.createStructField("input", DataTypes.StringType, false)
}));
df.select(
col("input"),
add_months(col("input"), 3).alias("add_months"),
add_months(col("input"), -3).alias("sub_months"),
date_add(col("input"), 4).alias("date_add"),
date_sub(col("input"), 4).alias("date_sub")
).show();
}
}
year(), month(), dayofweek(), dayofmonth(), dayofyear(), next_day(), weekofyear()
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;
import static org.apache.spark.sql.functions.*;
public class ExtractDatePartsExample {
public static void main(String[] args) {
SparkSession spark = SparkSession.builder()
.appName("Extract Date Parts Example")
.master("local[*]")
.getOrCreate();
Dataset<Row> df = spark.createDataFrame(Arrays.asList(
RowFactory.create("2019-01-23"),
RowFactory.create("2019-06-24"),
RowFactory.create("2019-09-20")
), DataTypes.createStructType(new StructField[]{
DataTypes.createStructField("input", DataTypes.StringType, false)
}));
df.select(
col("input"),
year(col("input")).alias("year"),
month(col("input")).alias("month"),
dayofweek(col("input")).alias("dayofweek"),
dayofmonth(col("input")).alias("dayofmonth"),
dayofyear(col("input")).alias("dayofyear"),
next_day(col("input"), "Sunday").alias("next_day"),
weekofyear(col("input")).alias("weekofyear")
).show();
}
}
Summary
In this lesson, we have covered the core date functions available in Spark SQL with Java. We discussed their importance in data handling, identified the most commonly used date functions, and provided examples of how to convert string values to date types and format dates as strings. Understanding these functions is vital for effective date manipulation and analysis in your data engineering tasks.