GoogleSQL for Bigtable supports the following format elements.
Format elements for date and time parts
Many GoogleSQL parsing and formatting functions rely on a format string to describe the format of parsed or formatted values. A format string represents the textual form of date and time and contains separate format elements that are applied left-to-right.
These functions use format strings:
Format strings generally support the following elements:
| Format element | Type | Description | Example |
|---|---|---|---|
%A
|
DATE
TIMESTAMP
|
The full weekday name (English). | Wednesday
|
%a
|
DATE
TIMESTAMP
|
The abbreviated weekday name (English). | Wed
|
%B
|
DATE
TIMESTAMP
|
The full month name (English). | January
|
%b
|
DATE
TIMESTAMP
|
The abbreviated month name (English). | Jan
|
%C
|
DATE
TIMESTAMP
|
The century (a year divided by 100 and truncated to an integer) as a decimal number (00-99). | 20
|
%c
|
TIMESTAMP
|
The date and time representation (English). | Wed Jan 20 21:47:00 2021
|
%D
|
DATE
TIMESTAMP
|
The date in the format %m/%d/%y. | 01/20/21
|
%d
|
DATE
TIMESTAMP
|
The day of the month as a decimal number (01-31). | 20
|
%e
|
DATE
TIMESTAMP
|
The day of month as a decimal number (1-31); single digits are preceded by a space. | 20
|
%F
|
DATE
TIMESTAMP
|
The date in the format %Y-%m-%d. | 2021-01-20
|
%G
|
DATE
TIMESTAMP
|
The ISO 8601 year with century as a decimal number. Each ISO year begins on the Monday before the first Thursday of the Gregorian calendar year. Note that %G and %Y may produce different results near Gregorian year boundaries, where the Gregorian year and ISO year can diverge. | 2021
|
%g
|
DATE
TIMESTAMP
|
The ISO 8601 year without century as a decimal number (00-99). Each ISO year begins on the Monday before the first Thursday of the Gregorian calendar year. Note that %g and %y may produce different results near Gregorian year boundaries, where the Gregorian year and ISO year can diverge. | 21
|
%H
|
TIMESTAMP
|
The hour (24-hour clock) as a decimal number (00-23). | 21
|
%h
|
DATE
TIMESTAMP
|
The abbreviated month name (English). | Jan
|
%I
|
TIMESTAMP
|
The hour (12-hour clock) as a decimal number (01-12). | 09
|
%J
|
DATE
TIMESTAMP
|
The ISO 8601 1-based day of the year (001-364 or 001-371 days). If the ISO year isn't set, this format element is ignored. | 364
|
%j
|
DATE
TIMESTAMP
|
The day of the year as a decimal number (001-366). | 020
|
%k
|
TIMESTAMP
|
The hour (24-hour clock) as a decimal number (0-23); single digits are preceded by a space. | 21
|
%l
|
TIMESTAMP
|
The hour (12-hour clock) as a decimal number (1-12); single digits are preceded by a space. | 9
|
%M
|
TIMESTAMP
|
The minute as a decimal number (00-59). | 47
|
%m
|
DATE
TIMESTAMP
|
The month as a decimal number (01-12). | 01
|
%n
|
All | A newline character. | |
%P
|
TIMESTAMP
|
When formatting, this is either am or pm. This can't be used with parsing. Instead, use %p. |
pm
|
%p
|
TIMESTAMP
|
When formatting, this is either AM or PM. When parsing, this can be used with am, pm, AM, or PM. |
PM
|
%Q
|
DATE
TIMESTAMP
|
The quarter as a decimal number (1-4). | 1
|
%R
|
TIMESTAMP
|
The time in the format %H:%M. | 21:47
|
%S
|
TIMESTAMP
|
The second as a decimal number (00-60). | 00
|
%s
|
TIMESTAMP
|
The number of seconds since 1970-01-01 00:00:00. Always overrides all other format elements, independent of where %s appears in the string. If multiple %s elements appear, then the last one takes precedence. | 1611179220
|
%T
|
TIMESTAMP
|
The time in the format %H:%M:%S. | 21:47:00
|
%t
|
All | A tab character. | |
%U
|
DATE
TIMESTAMP
|
The week number of the year (Sunday as the first day of the week) as a decimal number (00-53). | 03
|
%u
|
DATE
TIMESTAMP
|
The weekday (Monday as the first day of the week) as a decimal number (1-7). | 3
|
%V
|
DATE
TIMESTAMP
|
The ISO 8601 week number of the year (Monday as the first day of the week) as a decimal number (01-53). If the week containing January 1 has four or more days in the new year, then it's week 1; otherwise it's week 53 of the previous year, and the next week is week 1. | 03
|
%W
|
DATE
TIMESTAMP
|
The week number of the year (Monday as the first day of the week) as a decimal number (00-53). | 03
|
%w
|
DATE
TIMESTAMP
|
The weekday (Sunday as the first day of the week) as a decimal number (0-6). | 3
|
%X
|
TIMESTAMP
|
The time representation in HH:MM:SS format. | 21:47:00
|
%x
|
DATE
TIMESTAMP
|
The date representation in MM/DD/YY format. | 01/20/21
|
%Y
|
DATE
TIMESTAMP
|
The year with century as a decimal number. | 2021
|
%y
|
DATE
TIMESTAMP
|
The year without century as a decimal number (00-99), with an optional leading zero. Can be mixed with %C. If %C isn't specified, years 00-68 are 2000s, while years 69-99 are 1900s. | 21
|
%Z
|
TIMESTAMP
|
The time zone name. | UTC-5
|
%z
|
TIMESTAMP
|
The offset from the Prime Meridian in the format +HHMM or -HHMM as appropriate, with positive values representing locations east of Greenwich. | -0500
|
%%
|
All | A single % character. | %
|
%Ez
|
TIMESTAMP
|
RFC 3339-compatible numeric time zone (+HH:MM or -HH:MM). | -05:00
|
%E<number>S
|
TIMESTAMP
|
Seconds with <number> digits of fractional precision. | 00.000 for %E3S
|
%E*S
|
TIMESTAMP
|
Seconds with full fractional precision (a literal '*'). | 00.123456
|
%E4Y
|
DATE
TIMESTAMP
|
Four-character years (0001 ... 9999). Note that %Y produces as many characters as it takes to fully render the year. | 2021
|
Examples:
SELECT
FORMAT_DATE
(
"%b-%d-%Y"
,
DATE
"2008-12-25"
)
AS
formatted
;
/*-------------*
| formatted |
+-------------+
| Dec-25-2008 |
*-------------*/
SELECT
FORMAT_TIMESTAMP
(
"%b %Y %Ez"
,
TIMESTAMP
"2008-12-25 15:30:00+00"
)
AS
formatted
;
/*-----------------*
| formatted |
+-----------------+
| Dec 2008 +00:00 |
*-----------------*/
SELECT
PARSE_DATE
(
"%Y%m%d"
,
"20081225"
)
AS
parsed
;
/*------------*
| parsed |
+------------+
| 2008-12-25 |
*------------*/
SELECT
PARSE_TIMESTAMP
(
"%c"
,
"Thu Dec 25 07:30:00 2008"
)
AS
parsed
;
-- Display of results may differ, depending upon the environment and
-- time zone where this query was executed.
/*-------------------------*
| parsed |
+-------------------------+
| 2008-12-25 07:30:00 UTC |
*-------------------------*/
