DATE type

[philrz]

At the moment, the Super Data Model lacks a specific DATE type like SQL has. In the meantime, time types with a zeroed-out time portion can be used.

Details

Repro is with super commit 9fcbd22.

The DATE type is part of the SQL standard. For example, given input data.csv and a SQL tool:

$ cat data.csv 
some_date
2016-11-03

$ duckdb --version
v1.2.2 7c039464e4

$ duckdb -c "SELECT some_date,TYPEOF(some_date) FROM data.csv;"
┌────────────┬───────────────────┐
│ some_date  │ typeof(some_date) │
│    date    │      varchar      │
├────────────┼───────────────────┤
│ 2016-11-03 │ DATE              │
└────────────┴───────────────────┘

By comparison, at the moment in SuperDB, this is treated as a string type.

$ super -version
Version: 9fcbd222b

$ super -c "SELECT some_date,TYPEOF(some_date) FROM data.csv (FORMAT csv);"
{some_date:"2016-11-03","TYPEOF(some_date)":<string>}

If the value needs to be stored in a way that it can be used in functions that work with date/time values such as bucket or date_part, it can be cast to a time type that will have its time portion "zeroed out".

$ super -c "SELECT time(some_date) AS some_date FROM data.csv (FORMAT csv);"
{some_date:2016-11-03T00:00:00Z}

If such a time value needs trimmed to only the date portion (such as for output purposes), the strftime function may be helpful.

$ super -c "
SELECT time(some_date) AS some_date FROM data.csv (FORMAT csv)
| values strftime('%Y-%m-%d', some_date)"

"2016-11-03"

However, in a future revision of the data model and storage formats, an official DATE type is expected.