Skip to main content

Date & Time

Date and Time Data Types

NameAliasesStorage SizeResolutionMin ValueMax Value
DATE4 bytesday1000-01-019999-12-31
TIMESTAMPDATETIME8 bytesmicrosecond1000-01-01 00:00:009999-12-31 23:59:59.999999 UTC

Example

CREATE TABLE test_dt
(
date DATE,
ts TIMESTAMP
);

DESC test_dt;
+-------+--------------+------+---------+-------+
| Field | Type | Null | Default | Extra |
+-------+--------------+------+---------+-------+
| date | DATE | NO | 0 | |
| ts | TIMESTAMP | NO | 0 | |
+-------+--------------+------+---------+-------+

-- A TIMESTAMP value can optionally include a trailing fractional seconds part in up to microseconds (6 digits) precision.

INSERT INTO test_dt
VALUES ('2022-04-07',
'2022-04-07 01:01:01.123456'),
('2022-04-08',
'2022-04-08 01:01:01');

SELECT *
FROM test_dt;
+------------+----------------------------+
| date | ts |
+------------+----------------------------+
| 2022-04-07 | 2022-04-07 01:01:01.123456 |
| 2022-04-08 | 2022-04-08 01:01:01.000000 |
+------------+----------------------------+

-- Databend recognizes TIMESTAMP values in several formats.

CREATE TABLE test_formats
(
id INT,
a TIMESTAMP
);

INSERT INTO test_formats
VALUES (1,
'2022-01-01 02:00:11'),
(2,
'2022-01-02T02:00:22'),
(3,
'2022-02-02T04:00:03+00:00'),
(4,
'2022-02-03');

SELECT *
FROM test_formats;

----
1 2022-01-01 02:00:11.000000
2 2022-01-02 02:00:22.000000
3 2022-02-02 04:00:03.000000
4 2022-02-03 00:00:00.000000

-- Databend automatically adjusts and shows TIMESTAMP values based on your current timezone.

CREATE TABLE test_tz
(
id INT,
t TIMESTAMP
);

SET timezone='UTC';

INSERT INTO test_tz
VALUES (1,
'2022-02-03T03:00:00'),
(2,
'2022-02-03T03:00:00+08:00'),
(3,
'2022-02-03T03:00:00-08:00'),
(4,
'2022-02-03'),
(5,
'2022-02-03T03:00:00+09:00'),
(6,
'2022-02-03T03:00:00+06:00');

SELECT *
FROM test_tz;

----
1 2022-02-03 03:00:00.000000
2 2022-02-02 19:00:00.000000
3 2022-02-03 11:00:00.000000
4 2022-02-03 00:00:00.000000
5 2022-02-02 18:00:00.000000
6 2022-02-02 21:00:00.000000

SET timezone='Asia/Shanghai';

SELECT *
FROM test_tz;

----
1 2022-02-03 11:00:00.000000
2 2022-02-03 03:00:00.000000
3 2022-02-03 19:00:00.000000
4 2022-02-03 08:00:00.000000
5 2022-02-03 02:00:00.000000
6 2022-02-03 05:00:00.000000

Functions

See Date & Time Functions.

Formatting Date and Time

In Databend, certain date and time functions like TO_DATE and TO_TIMESTAMP require you to specify the desired format for date and time values. To handle date and time formatting, Databend makes use of the chrono::format::strftime module, which is a standard module provided by the chrono library in Rust. This module enables precise control over the formatting of dates and times. The following content is excerpted from https://docs.rs/chrono/latest/chrono/format/strftime/index.html:

Spec.ExampleDescription
DATE SPECIFIERS:
%Y2001The full proleptic Gregorian year, zero-padded to 4 digits. chrono supports years from -262144 to 262143. Note: years before 1 BCE or after 9999 CE, require an initial sign (+/-).
%C20The proleptic Gregorian year divided by 100, zero-padded to 2 digits.
%y01The proleptic Gregorian year modulo 100, zero-padded to 2 digits.
%m07Month number (01–12), zero-padded to 2 digits.
%bJulAbbreviated month name. Always 3 letters.
%BJulyFull month name. Also accepts corresponding abbreviation in parsing.
%hJulSame as %b.
%d08Day number (01–31), zero-padded to 2 digits.
%e8Same as %d but space-padded. Same as %_d.
%aSunAbbreviated weekday name. Always 3 letters.
%ASundayFull weekday name. Also accepts corresponding abbreviation in parsing.
%w0Sunday = 0, Monday = 1, …, Saturday = 6.
%u7Monday = 1, Tuesday = 2, …, Sunday = 7. (ISO 8601)
%U28Week number starting with Sunday (00–53), zero-padded to 2 digits.
%W27Same as %U, but week 1 starts with the first Monday in that year instead.
%G2001Same as %Y but uses the year number in ISO 8601 week date.
%g01Same as %y but uses the year number in ISO 8601 week date.
%V27Same as %U but uses the week number in ISO 8601 week date (01–53).
%j189Day of the year (001–366), zero-padded to 3 digits.
%D07/08/01Month-day-year format. Same as %m/%d/%y.
%x07/08/01Locale’s date representation (e.g., 12/31/99).
%F2001-07-08Year-month-day format (ISO 8601). Same as %Y-%m-%d.
%v8-Jul-2001Day-month-year format. Same as %e-%b-%Y.
TIME SPECIFIERS:
%H00Hour number (00–23), zero-padded to 2 digits.
%k0Same as %H but space-padded. Same as %_H.
%I12Hour number in 12-hour clocks (01–12), zero-padded to 2 digits.
%l12Same as %I but space-padded. Same as %_I.
%Pamam or pm in 12-hour clocks.
%pAMAM or PM in 12-hour clocks.
%M34Minute number (00–59), zero-padded to 2 digits.
%S60Second number (00–60), zero-padded to 2 digits.
%f026490000The fractional seconds (in nanoseconds) since last whole second.
%.f.026490Similar to .%f but left-aligned. These all consume the leading dot.
%.3f.026Similar to .%f but left-aligned but fixed to a length of 3.
%.6f.026490Similar to .%f but left-aligned but fixed to a length of 6.
%.9f.026490000Similar to .%f but left-aligned but fixed to a length of 9.
%3f026Similar to %.3f but without the leading dot.
%6f026490Similar to %.6f but without the leading dot.
%9f026490000Similar to %.9f but without the leading dot.
%R00:34Hour-minute format. Same as %H:%M.
%T00:34:60Hour-minute-second format. Same as %H:%M:%S.
%X00:34:60Locale’s time representation (e.g., 23:13:48).
%r12:34:60 AMHour-minute-second format in 12-hour clocks. Same as %I:%M:%S %p.
TIME ZONE SPECIFIERS:
%ZACSTLocal time zone name. Skips all non-whitespace characters during parsing.
%z+0930Offset from the local time to UTC (with UTC being +0000).
%:z+09:30Same as %z but with a colon.
%::z+09:30:00Offset from the local time to UTC with seconds.
%:::z+09Offset from the local time to UTC without minutes.
%#z+09Parsing only: Same as %z but allows minutes to be missing or present.
DATE & TIME SPECIFIERS:
%cSun Jul 8 00:34:60 2001Locale’s date and time (e.g., Thu Mar 3 23:05:25 2005).
%+2001-07-08T00:34:60.026490+09:30ISO 8601 / RFC 3339 date & time format.
%s994518299UNIX timestamp, the number of seconds since 1970-01-01 00:00 UTC.
SPECIAL SPECIFIERS:
%tLiteral tab (\t).
%nLiteral newline (\n).
%%Literal percent sign.

It is possible to override the default padding behavior of numeric specifiers %?. This is not allowed for other specifiers and will result in the BAD_FORMAT error.

ModifierDescription
%-?Suppresses any padding including spaces and zeroes. (e.g. %j = 012, %-j = 12)
%_?Uses spaces as a padding. (e.g. %j = 012, %_j = 12)
%0?Uses zeroes as a padding. (e.g. %e = 9, %0e = 09)
  • %C, %y: This is floor division, so 100 BCE (year number -99) will print -1 and 99 respectively.

  • %U: Week 1 starts with the first Sunday in that year. It is possible to have week 0 for days before the first Sunday.

  • %G, %g, %V: Week 1 is the first week with at least 4 days in that year. Week 0 does not exist, so this should be used with %G or %g.

  • %S: It accounts for leap seconds, so 60 is possible.

  • %f, %.f, %.3f, %.6f, %.9f, %3f, %6f, %9f: The default %f is right-aligned and always zero-padded to 9 digits for the compatibility with glibc and others, so it always counts the number of nanoseconds since the last whole second. E.g. 7ms after the last second will print 007000000, and parsing 7000000 will yield the same.

    The variant %.f is left-aligned and print 0, 3, 6 or 9 fractional digits according to the precision. E.g. 70ms after the last second under %.f will print .070 (note: not .07), and parsing .07, .070000 etc. will yield the same. Note that they can print or read nothing if the fractional part is zero or the next character is not ..

    The variant %.3f, %.6f and %.9f are left-aligned and print 3, 6 or 9 fractional digits according to the number preceding f. E.g. 70ms after the last second under %.3f will print .070 (note: not .07), and parsing .07, .070000 etc. will yield the same. Note that they can read nothing if the fractional part is zero or the next character is not . however will print with the specified length.

    The variant %3f, %6f and %9f are left-aligned and print 3, 6 or 9 fractional digits according to the number preceding f, but without the leading dot. E.g. 70ms after the last second under %3f will print 070 (note: not 07), and parsing 07, 070000 etc. will yield the same. Note that they can read nothing if the fractional part is zero.

  • %Z: Offset will not be populated from the parsed data, nor will it be validated. Timezone is completely ignored. Similar to the glibc strptime treatment of this format code.

    It is not possible to reliably convert from an abbreviation to an offset, for example CDT can mean either Central Daylight Time (North America) or China Daylight Time.

  • %+: Same as %Y-%m-%dT%H:%M:%S%.f%:z, i.e. 0, 3, 6 or 9 fractional digits for seconds and colons in the time zone offset.

    This format also supports having a Z or UTC in place of %:z. They are equivalent to +00:00.

    Note that all T, Z, and UTC are parsed case-insensitively.

    The typical strftime implementations have different (and locale-dependent) formats for this specifier. While Chrono’s format for %+ is far more stable, it is best to avoid this specifier if you want to control the exact output.

  • %s: This is not padded and can be negative. For the purpose of Chrono, it only accounts for non-leap seconds so it slightly differs from ISO C strftime behavior.