datetime

Dates, times, timestamps, durations, and timezone offsets

8 categories, 89 types: 69 with a format string, 12 a sibling in its category also accepts, none accepted by another category's rule, 18 standard in CLDR locales, 7 that decompose into parts, 1 in Meridian's open data.

8 categories · 89 types

The 8 datetime categories

Category	Types	Casts to	For example	Within the group
date	40	DATE	04-03-24	11 a sibling accepts, 11 misread
timestamp	31	TIMESTAMP, TIMESTAMPTZ, VARCHAR	2024-03-04 05:06:07.123456+0…	1 a sibling also accepts
time	5	TIME	05:06 AM	each told apart
component	4	VARCHAR, SMALLINT	Monday	each told apart
epoch	3	TIMESTAMP	1705312200000000	each told apart
offset	3	VARCHAR	America/New_York	each told apart
period	2	DATE, VARCHAR	FY2024	each told apart
duration	1	INTERVAL	PT30M	—

Where datetime categories overlap

No value a type writes is accepted by another category's rule.

Which datetime type is it?

3 of 89 datetime types in 1 category accept it, and read it 3 ways.

DuckDB cannot run one of these types' rules, so the box cannot say whether it accepts a value.

Day of Week Name, Full Month Name and Periodicity carry no pattern or format, so they have no rule to ask.

Type	Category	Reads it as
Short Date (Day-Month-Year, 2-digit year) `short_dmy`	date	2024-03-04
Short Date (Month-Day-Year, 2-digit year) `short_mdy`	date	2024-04-03
Short Date (Year-Month-Day, 2-digit year) `short_ymd`	date	2004-03-24

What the engine extracts

date5 of 40era · era_name · era_year · month · year
period2 of 2quarter · year

In Meridian's open data

edgar_gleif.as_of iso
gleif.initial_registration iso.next_renewal iso

Use it

CLI · DuckDB

$ for v in '04-03-24' '2024-03-04 05:06:07.123456+00' '05:06 AM' 'Monday' '1705312200000000' 'America/New_York' 'FY2024' 'PT30M'; do finetype infer -i "$v"; done

SELECT v, ft_infer(v) FROM (VALUES ('04-03-24'), ('2024-03-04 05:06:07.123456+00'), ('05:06 AM'), ('Monday'), ('1705312200000000'), ('America/New_York'), ('FY2024'), ('PT30M')) t(v);