CODEWITHSUNDEEP
sqlpysparkapache-spark-sql
younus
younus

Reputation: 474

converting 24 hrs date format in pyspark issue

I was trying to cast a string format datatype into date format in spark SQL and below is the query which i used to covert but strangely its working for 12 hrs format and not for 24 hr format(displaying as null)

select from_unixtime(unix_timestamp('19-Aug-2020 10:05:40', 'dd-MMM-yyyy hh:mm:ss'), 'yyyy-MM-dd hh:mm:ss') as timestamp1,from_unixtime(unix_timestamp('19-Aug-2020 14:05:40', 'dd-MMM-yyyy hh:mm:ss'), 'yyyy-MM-dd hh:mm:ss') as timestamp2;

enter image description here

Am i missing something here??

Thanks in Advance

Upvotes: 2

Views: 2482

Answers (3)

Arif Rizwan
Arif Rizwan

Reputation: 41

We can create out own date formatter by using split as below

Select date.split('-')[2]||'-'||case when length(date.split('-')[0]) = 1 then '0'||date.split('-')[0] else  date.split('-')[0] end  || case when length(date.split('-')[1]) = 1 then '0'||date.split('-')[1] else  date.split('-')[1] end]

Date = date_column

Date format yyyy-mm-dd
Delimiter can be different.

Without any date format function usage

Upvotes: 0

Arif Rizwan
Arif Rizwan

Reputation: 41

We can use splitby

Select date.split('-')[2]||'-'||case when length(date.split('-')[0]) = 1 then '0'||date.split('-')[0] else  date.split('-')[0] end  || case when length(date.split('-')[1]) = 1 then '0'||date.split('-')[1] else  date.split('-')[1] end] 
Date = date_column

Date format yyyy-mm-dd
Delimiter can be different.

Without any date format

Upvotes: 0

Lamanus
Lamanus

Reputation: 13551

Here is the test for HH and hh.

spark.sql('''
    select 
        to_timestamp('19-Aug-2020 10:05:40', 'dd-MMM-yyyy hh:mm:ss') as timestamp11,
        to_timestamp('19-Aug-2020 10:05:40', 'dd-MMM-yyyy HH:mm:ss') as timestamp12,
        to_timestamp('19-Aug-2020 14:05:40', 'dd-MMM-yyyy hh:mm:ss') as timestamp21,
        to_timestamp('19-Aug-2020 14:05:40', 'dd-MMM-yyyy HH:mm:ss') as timestamp22
''').show()

+-------------------+-------------------+-----------+-------------------+
|        timestamp11|        timestamp12|timestamp21|        timestamp22|
+-------------------+-------------------+-----------+-------------------+
|2020-08-19 10:05:40|2020-08-19 10:05:40|       null|2020-08-19 14:05:40|
+-------------------+-------------------+-----------+-------------------+

Upvotes: 2

Related Questions