Question about sqlite time,timestamp types

[gzp79]

In sqlite (almost) all the datetime, timestamp are using text for column types. Is it intentional ?
Sqlite has types like TIMESTAMPTZ, etc. Why aren't they used ?

to work around this I have to write codes like

#[derive(Iden)]
#[iden = "TIMESTAMPTZ"]
pub struct TimestampTZ;

Table::create().col(ColumnDef::new(Sources::Sync).custom(TimestampTZ).not_null())

[billy1624]

Hi @gzp-crey, welcome!!

2.2. Date and Time Datatype
SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

Reference

• https://www.sqlite.org/datatypes.html
• https://www.sqlite.org/datatype3.html
• https://docs.rs/sqlx/0.5.7/sqlx/sqlite/types/index.html
• https://docs.rs/sqlx/0.5.7/sqlx/struct.Sqlite.html

[gzp79]

That's funny. I ported some sql from postgres for local dev and never checked what TIMESTAMPTZ really is.
It seems to be only some typedef/alias to text, but could not find any info/detail about it:

SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE table foo(t TIMESTAMPTZ);
sqlite> PRAGMA table_info(foo);
0|t|TIMESTAMPTZ|0||0
sqlite> insert into foo values(datetime('now'));
sqlite> select * from foo;
2021-09-30 10:52:52
sqlite> select typeof(t), typeof(datetime('now')) from foo;
text|text
sqlite>

So it seems as replacing it to a text should work just fine.
Thanks and sorry for the disturbance.