Spark SQL - Convert Date/Timestamp to String via date_format Function
Spark SQL function
date_format
can be used to convert date or timestamp to string with certain format. Date to string
spark-sql> select date_format(DATE'2022-01-01','dd MMM yyyy'); date_format(CAST(DATE '2022-01-01' AS TIMESTAMP), dd MMM yyyy) 01 Jan 2022
Timestamp to string
spark-sql> select date_format(TIMESTAMP'2022-01-01 13:30:45','dd MMM yyyy, HH:mm a'); date_format(TIMESTAMP '2022-01-01 13:30:45', dd MMM yyyy, HH:mm a) 01 Jan 2022, 13:30 PM
Use cast function
We can also use cast
function to convert date/timestamp to string as the following code snippet shows:
spark-sql> select cast(DATE'2022-01-01' as string); CAST(DATE '2022-01-01' AS STRING) 2022-01-01
DateTimeFormatter
All the available format symbols are listed below:
Symbol Meaning Presentation Examples ------ ------- ------------ ------- G era text AD; Anno Domini; A u year year 2004; 04 y year-of-era year 2004; 04 D day-of-year number 189 M/L month-of-year number/text 7; 07; Jul; July; J d day-of-month number 10 Q/q quarter-of-year number/text 3; 03; Q3; 3rd quarter Y week-based-year year 1996; 96 w week-of-week-based-year number 27 W week-of-month number 4 E day-of-week text Tue; Tuesday; T e/c localized day-of-week number/text 2; 02; Tue; Tuesday; T F week-of-month number 3 a am-pm-of-day text PM h clock-hour-of-am-pm (1-12) number 12 K hour-of-am-pm (0-11) number 0 k clock-hour-of-am-pm (1-24) number 0 H hour-of-day (0-23) number 0 m minute-of-hour number 30 s second-of-minute number 55 S fraction-of-second fraction 978 A milli-of-day number 1234 n nano-of-second number 987654321 N nano-of-day number 1234000000 V time-zone ID zone-id America/Los_Angeles; Z; -08:30 z time-zone name zone-name Pacific Standard Time; PST O localized zone-offset offset-O GMT+8; GMT+08:00; UTC-08:00; X zone-offset 'Z' for zero offset-X Z; -08; -0830; -08:30; -083015; -08:30:15; x zone-offset offset-x +0000; -08; -0830; -08:30; -083015; -08:30:15; Z zone-offset offset-Z +0000; -0800; -08:00; p pad next pad modifier 1 ' escape for text delimiter '' single quote literal ' [ optional section start ] optional section end # reserved for future use { reserved for future use } reserved for future use
copyright
This page is subject to Site terms.
comment Comments
No comments yet.
Log in with external accounts
warning Please login first to view stats information.
article
Spark SQL - DENSE_RANK Window Function
code
PySpark DataFrame - Convert JSON Column to Row using json_tuple
article
Spark SQL - Date/Timestamp Conversation from/to UNIX Date/Timestamp
article
Spark SQL - isnull and isnotnull Functions
article
Spark SQL - Convert Object to JSON String
Read more (46)