PySpark SQL – Working with Unix Time | Timestamp

In PySpark SQL, unix_timestamp() is used to get the current time and to convert the time string in a format yyyy-MM-dd HH:mm:ss to Unix timestamp (in seconds) and from_unixtime() is used to convert the number of seconds from Unix epoch (1970-01-01 00:00:00 UTC) to a string representation of the timestamp.

Both unix_timestamp() & from_unixtime() can be used on PySQL SQL & DataFrame and these use the default timezone and the default locale of the system.

• What is Unix Time (Epoch Time)
• unix_timestamp() Function
• from_unixtime() Function

Unix Time (Epoch Time)

Note that Unix Epoch time doesn’t support a fraction of the second which is represented with SSS.

Before we start the usage of Unix Epoch Time, let’s Create a PySpark DataFrame with different Dates and Times. Note that our examples also don’t have a fraction of the second (SSS).

inputData = [("2019-07-01 12:01:19",
            "07-01-2019 12:01:19", 
            "07-01-2019")]
columns=["timestamp_1","timestamp_2","timestamp_3"]
df=spark.createDataFrame(
        data = inputData,
        schema = columns)
df.printSchema()
df.show(truncate=False)

To make it simple for our example, I’ve named the columns as timestamp_1,timestamp_2,timestamp_3. From above, printSchema() and show() on DataFrame yields below output.

root
 |-- timestamp_1: string (nullable = true)
 |-- timestamp_2: string (nullable = true)
 |-- timestamp_3: string (nullable = true)

+-------------------+-------------------+-----------+
|timestamp_1        |timestamp_2        |timestamp_3|
+-------------------+-------------------+-----------+
|2019-07-01 12:01:19|07-01-2019 12:01:19|07-01-2019 |
+-------------------+-------------------+-----------+

unix_timestamp() – Converts Date and Timestamp Column to Unix Time

Use PySpark SQL function unix_timestamp() is used to get the current time and to convert the time string in format yyyy-MM-dd HH:mm:ss to Unix timestamp (in seconds) by using the current timezone of the system.

Syntax:

1) def unix_timestamp()
2) def unix_timestamp(s: Column)
3) def unix_timestamp(s: Column, p: String)

1. Returns the current time in Unix timestamp seconds (epoch time).
2. Returns the Unix seconds in Long from the date/time column.
3. Returns the Unix seconds in Long from the date/time column, use the second argument to specify the date/time format of the first argument.

#Convert timestamp to unix timestamp
from pyspark.sql.functions import *
df2 = df.select( 
      unix_timestamp(col("timestamp_1")).alias("timestamp_1"), 
      unix_timestamp(col("timestamp_2"),"MM-dd-yyyy HH:mm:ss").alias("timestamp_2"), 
      unix_timestamp(col("timestamp_3"),"MM-dd-yyyy").alias("timestamp_3"), 
      unix_timestamp().alias("timestamp_4") 
   )
df2.printSchema()
df2.show(truncate=False)

Yields below output. As you notice, the above example uses all three unix_timestamp() syntaxes.

root
 |-- timestamp_1: long (nullable = true)
 |-- timestamp_2: long (nullable = true)
 |-- timestamp_3: long (nullable = true)
 |-- timestamp_4: long (nullable = true)

+-----------+-----------+-----------+-----------+
|timestamp_1|timestamp_2|timestamp_3|timestamp_4|
+-----------+-----------+-----------+-----------+
|1562007679 |1562007679 |1561964400 |1577166257 |
+-----------+-----------+-----------+-----------+

from_unixtime() – Converts Unix Time Seconds to Date and Timestamp

Spark SQL Function from_unixtime() is used to convert the Unix timestamp to a String representing Date and Timestamp, in other words, it converts the Epoch time in seconds to date and timestamp.

Syntax:

def from_unixtime(ut: Column): Column
def from_unixtime(ut: Column, f: String): Column

This function has 2 signatures, the first one returns the Timestamp in a default format yyyy-MM-dd HH:mm:ss and the second one returns in a format specified in an argument.

// Convert Unix timestamp to timestamp
df3=df2.select(
    from_unixtime(col("timestamp_1")).alias("timestamp_1"),
    from_unixtime(col("timestamp_2"),"MM-dd-yyyy HH:mm:ss").alias("timestamp_2"),
    from_unixtime(col("timestamp_3"),"MM-dd-yyyy").alias("timestamp_3"),
    from_unixtime(col("timestamp_4")).alias("timestamp_4")
  )
df3.printSchema()
df3.show(truncate=False)

Yields below output. This snippet also uses both syntaxes of from_unixtime() function.

+-------------------+-------------------+-----------+-------------------+
|timestamp_1        |timestamp_2        |timestamp_3|timestamp_4        |
+-------------------+-------------------+-----------+-------------------+
|2019-07-01 12:01:19|07-01-2019 12:01:19|07-01-2019 |2021-02-26 17:27:45|
+-------------------+-------------------+-----------+-------------------+

Source code for reference

from pyspark.sql import SparkSession

# Create SparkSession
spark = SparkSession.builder \
          .appName('SparkByExamples.com') \
          .getOrCreate()

inputData = [("2019-07-01 12:01:19",
            "07-01-2019 12:01:19", 
            "07-01-2019")]
columns=["timestamp_1","timestamp_2","timestamp_3"]
df=spark.createDataFrame(
        data = inputData,
        schema = columns)
df.printSchema()
df.show(truncate=False)

from pyspark.sql.functions import *
df2 = df.select( 
      unix_timestamp(col("timestamp_1")).alias("timestamp_1"), 
      unix_timestamp(col("timestamp_2"),"MM-dd-yyyy HH:mm:ss").alias("timestamp_2"), 
      unix_timestamp(col("timestamp_3"),"MM-dd-yyyy").alias("timestamp_3"), 
      unix_timestamp().alias("timestamp_4") 
   )
df2.printSchema()
df2.show(truncate=False)

df3=df2.select(
    from_unixtime(col("timestamp_1")).alias("timestamp_1"),
    from_unixtime(col("timestamp_2"),"MM-dd-yyyy HH:mm:ss").alias("timestamp_2"),
    from_unixtime(col("timestamp_3"),"MM-dd-yyyy").alias("timestamp_3"),
    from_unixtime(col("timestamp_4")).alias("timestamp_4")
  )
df3.printSchema()
df3.show(truncate=False)

Conclusion

In this article, you have learned about Unix Time, how to use the Spark SQL function unix_timestamp() to convert Date and Timestamp to Unix Time seconds, and from_unixtime() function to convert Unix Time seconds to Date and Timestamp back.

Related Articles:

• PySpark SQL – How to Get Current Date & Timestamp
• PySpark SQL – Date and Timestamp Functions
• PySpark SQL – Convert Date to String Format
• PySpark SQL – Convert String to Date Format
• PySpark SQL – Convert Timestamp to Date
• PySpark SQL – Convert String to Timestamp
• PySpark Read and Write SQL Server Table
• PySpark Read and Write MySQL Database Table