Extract time from Date Time as a separate column

My table looks like this –

DateTime	ID
2010-12-01 08:26:00	34
2010-12-01 09:41:00	42

I want to extract the time from DateTime and create a third column of it and then group it with frequency counts. Is there a way to do this in SQL? I’m using Apache Spark with inline SQL. I have achieved the equivalent using Spark functions like this –

df2 = df.withColumn('Time', date_format('DateTime', 'HH:mm:ss'))
df3 = df2.groupBy("Time").count() // To get count under every hour
df3.show()

​x
 
df2 = df.withColumn('Time', date_format('DateTime', 'HH:mm:ss'))df3 = df2.groupBy("Time").count() // To get count under every hourdf3.show()​

Answer

If you want to do it in SQL, you can do:

df.createOrReplaceTempView('df')
result = spark.sql("select date_format(DateTime, 'HH:mm:ss') as Time, count(*) as cnt from df group by Time")

 
df.createOrReplaceTempView('df')result = spark.sql("select date_format(DateTime, 'HH:mm:ss') as Time, count(*) as cnt from df group by Time")​

Advertisement

Answer