How to create Date and Hour columns from Seconds column using SQL

I have a column called Time with float values giving time in seconds after the first event occurred. I was wondering how to create columns called Date and Hour using this column in SQL.

My dataset is big, I can not use Pandas.

Setup

import numpy as np
import pandas as pd

import pyspark
from pyspark.sql.functions import col
from pyspark.sql.functions import udf # @udf("integer") def myfunc(x,y): return x - y
from pyspark.sql import functions as F # stddev format_number date_format, dayofyear, when


spark = pyspark.sql.SparkSession.builder.appName('bhishan').getOrCreate()

Data

%%bash

cat > data.csv << EOL
Time
10.0
61.0
3500.00
3600.00
3700.54
7000.22
7200.22
15000.55
86400.22
EOL

pyspark dataframe

df = spark.read.csv('data.csv', header=True, inferSchema=True)
print('nrows = ', df.count(), 'ncols = ', len(df.columns))
df.show()
nrows =  9 ncols =  1
+--------+
|    Time|
+--------+
|    10.0|
|    61.0|
|  3500.0|
|  3600.0|
| 3700.54|
| 7000.22|
| 7200.22|
|15000.55|
|86400.22|
+--------+

Using pandas (but I need pyspark)

pandas_df = df.toPandas()
pandas_df['Date'] = pd.to_datetime('2019-01-01') + pd.to_timedelta(pandas_df['Time'],unit='s')

pandas_df['hour'] = pandas_df['Date'].dt.hour
print(pandas_df)
       Time                    Date  hour
0     10.00 2019-01-01 00:00:10.000     0
1     61.00 2019-01-01 00:01:01.000     0
2   3500.00 2019-01-01 00:58:20.000     0
3   3600.00 2019-01-01 01:00:00.000     1
4   3700.54 2019-01-01 01:01:40.540     1
5   7000.22 2019-01-01 01:56:40.220     1
6   7200.22 2019-01-01 02:00:00.220     2
7  15000.55 2019-01-01 04:10:00.550     4
8  86400.22 2019-01-02 00:00:00.220     0

Question

How to get the new column Date and Hour using SQL and Pyspark like I just did in pandas. I have big data that I can not use pandas and I have to use pyspark for that. Thanks.

Answer

You can use functions: timestamp, unix_timestamp and hour:

from pyspark.sql.functions import expr, hour

df.withColumn('Date', expr("timestamp(unix_timestamp('2019-01-01 00:00:00') + Time)")) 
  .withColumn('hour', hour('Date')) 
  .show(truncate=False)                                              

+--------+----------------------+----+
|Time    |Date                  |hour|
+--------+----------------------+----+
|10.0    |2019-01-01 00:00:10   |0   |
|61.0    |2019-01-01 00:01:01   |0   |
|3500.0  |2019-01-01 00:58:20   |0   |
|3600.0  |2019-01-01 01:00:00   |1   |
|3700.54 |2019-01-01 01:01:40.54|1   |
|7000.22 |2019-01-01 01:56:40.22|1   |
|7200.22 |2019-01-01 02:00:00.22|2   |
|15000.55|2019-01-01 04:10:00.55|4   |
|86400.22|2019-01-02 00:00:00.22|0   |
+--------+----------------------+----+

Note: use timestamp function to keep the microsecond

Use SQL syntax:

df.createOrReplaceTempView('t_df')

spark.sql(""" 
    WITH d AS (SELECT *, timestamp(unix_timestamp('2019-01-01 00:00:00') + Time) as Date FROM t_df) 
    SELECT *, hour(d.Date) AS hour FROM d   
""").show(truncate=False)