Datetime format strings - AWS Clean Rooms

Datetime format strings

You can use datetime patterns in the following common scenarios:

  • When working with CSV and JSON data sources to parse and format datetime content

  • When converting between string types and date or timestamp types using functions such as:

    • unix_timestamp

    • date_format

    • to_unix_timestamp

    • from_unixtime

    • to_date

    • to_timestamp

    • from_utc_timestamp

    • to_utc_timestamp

Use the pattern letters in the following table for date and timestamp parsing and formatting.

Datepart or timepart Meaning Examples
a AM or PM of the day, presented as am-pm PM
D Day of the year, presented as a 3-digit number

189
d Day of the month, presented as a 2-digit number 28
E Day of the week, presented as a text

Tue

Tuesday
F Aligned day of the week in the month, presented as a 1-digit number 3
G Era indicator, presented as text

AD

Anno Domini
h Clock-hour of AM or PM, presented as a 2-digit number 12
H Hour of day, presented as a 2-digit number from 0–23 0
k Clock-hour of day, presented as a 2-digit number from 1–24 1
K Hour of AM or PM, presented as a 2-digit number from 0–11 0
m Minute of hour, presented as a 2-digit number 30
M/L Month of the year, presented as a month

7

07

Jul

July
O Localized zone offset from UTC

GMT+8

GMT+8:00

UTC-08:00
Q/q Quarter of the year, presented as a number (1 to 4) or text

3

03

Q3

3rd quarter
s Second of minute, presented as a 2-digit number 55
S Fraction of a second, presented as a fraction 978
V Time zone identifier, presented as a zone-id

America/Los_Angeles

Z

08:30
x Zone offset from UTC (offset-X)

+0000

-08

-0830

-08:30

-083015

-08:30:15
X Zone offset from UTC; where Z is for zero

Z

-08

-0830

-08:30

-083015

-08:30:15
y Year, presented as a year

2020

20
z Time zone name, presented as text

Pacific Standard Time

PST
Z Zone offset from UTC (offset-Z)

+0000

-0800

-08:00
' Escape for text, presented as a delimiter N/A
'' Single quote, presented as a literal '
[ Optional section start N/A
] Optional section end N/A

The number of pattern letters determines the format type:

Text Format

  • Use 1-3 letters for the abbreviated form (for example, "Mon" for Monday)

  • Use exactly 4 letters for the full form (for example, "Monday")

  • Don't use 5 or more letters - this will cause an error

Number Format (n)

  • The value n represents the maximum number of letters allowed

  • For single letter patterns:

    • Output uses minimum digits without padding

  • For multiple letter patterns:

    • Output is padded with zeros to match the letter count width

  • When parsing, input must contain the exact number of digits

Number/Text Format

  • For 3 or more letters, follow the Text Format rules

  • For fewer letters, follow the Number Format rules

Fraction Format

  • Use 1-9 'S' characters (for example, SSSSSS)

  • For parsing:

    • Accept fractions between 1 and the number of S characters

  • For formatting:

    • Pad with zeros to match the number of S characters

  • Supports up to 6 digits for microsecond precision

  • Can parse nanoseconds but truncates extra digits

Year Format

  • The letter count sets the minimum field width for padding

  • For two letters:

    • Prints the last two digits

    • Parses years between 2000-2099

  • For less than four letters (except two):

    • Shows the sign only for negative years

  • Don't use 7 or more letters - this will cause an error

Month Format

  • Use 'M' for standard form or 'L' for standalone form

  • Single 'M' or 'L':

    • Shows month numbers 1-12 without padding

  • 'MM' or 'LL':

    • Shows month numbers 01-12 with padding

  • 'MMM':

    • Shows abbreviated month name in standard form

    • Must be part of a full date pattern

  • 'LLL':

    • Shows abbreviated month name in standalone form

    • Use for month-only formatting

  • 'MMMM':

    • Shows full month name in standard form

    • Use for dates and timestamps

  • 'LLLL':

    • Shows full month name in standalone form

    • Use for month-only formatting

Time Zone Formats

  • am-pm: Use 1 letter only

  • Zone ID (V): Use 2 letters only

  • Zone names (z):

    • 1-3 letters: Shows short name

    • 4 letters: Shows full name

    • Don't use 5 or more letters

Offset Formats

  • X and x:

    • 1 letter: Shows hour (+01) or hour-minute (+0130)

    • 2 letters: Shows hour-minute without colon (+0130)

    • 3 letters: Shows hour-minute with colon (+01:30)

    • 4 letters: Shows hour-minute-second without colon (+013015)

    • 5 letters: Shows hour-minute-second with colon (+01:30:15)

    • X uses 'Z' for zero offset

    • x uses '+00', '+0000', or '+00:00' for zero offset

  • O:

    • 1 letter: Shows short form (GMT+8)

    • 4 letters: Shows full form (GMT+08:00)

  • Z:

    • 1-3 letters: Shows hour-minute without colon (+0130)

    • 4 letters: Shows full localized form

    • 5 letters: Shows hour-minute-second with colon

Optional Sections

  • Use square brackets [ ] to mark optional content

  • You can nest optional sections

  • All valid data appears in output

  • Input can omit entire optional sections

Note

The symbols 'E', 'F', 'q', and 'Q' work only for datetime formatting (like date_format). Don't use them for datetime parsing (like to_timestamp).