timestamptz format
I have searched for quite some time but I can't seem to figure it out. I can store a timestamptz with many formats and supabase will handle it and thats fine.
My main problem is I want to parse the timestamptz but I dont know the exact pattern.
Is it "%Y-%m-%d %H:%M:%S%.f%z"
Identifiers used are defined here: https://docs.rs/chrono/latest/chrono/format/strftime/index.html
It would be of great help if you could help me define it in the above defined syntax.
Here is a example of how the time was stored (this was exported via csv):
(with default value now)
id,test,timestamp
0,[],2024-01-15 21:38:27.213678+00
(with default value (now() at time zone 'utc'))
id,test,timestamp
1,[],2024-01-15 21:40:52.21653+00
//They are literally the same
I am living in germany btw.
chrono::format::strftime - Rust
strftime/strptime-inspired date and time formatting syntax.85 Replies
It is ISO-8601 with the addition that the
T separator can be a space.as far as i've read iso-8601 is pretty diverse
there if rfc3339 for example which is a application ofit
The DB default timezone is UTC, so your two examples are identical
Is there a way to tell the db to use the rfc3339 timestamp when createing a timestamp?
Postgres is really good at timezones and timestamps. I really wish the APIs Supabase provdes would leverage them better.
You can format the timestamp however you want when querying. The internal storage is irrelevent.
with as_char right
yeah.
now comes the next problem
i havent found any good as_char conversion function to pull it of meaning to convert to the rfc3339 definition
Do you need to do it in the DB or your backend app server (or even the client). Javascript parses the dates no problem, for example.
What are you trying to format it to be?
DB or backend either is fine
tbh rfc3339 or rfc2822 would be cool
cause would make my life easier in rust.
chrono has these 2 functions:
DateTime in chrono - Rust
ISO 8601 combined date and time with time zone.
DateTime in chrono - Rust
ISO 8601 combined date and time with time zone.
RFC 3999 is suppposed to allow space instead of "T" but I cannot see that in a quick glance (found it). The timestamptz is already in this format.
and maybe possibly the timezone offset in the RFC requires the ":MM" for minutes which Postgres is not providing. that would be really pedantic if your parser didn't accept that.
" This date/time format may be used in some environments or contexts
that distinguish between the upper- and lower-case letters 'A'-'Z'
and 'a'-'z' (e.g. XML). Specifications that use this format in
such environments MAY further limit the date/time syntax so that
the letters 'T' and 'Z' used in the date/time syntax must always
be upper case. Applications that generate this format SHOULD use
upper case letters.
NOTE: ISO 8601 defines date and time separated by "T".
Applications using this syntax may choose, for the sake of
readability, to specify a full-date and full-time separated by
(say) a space character."
per iso its okay
but the application doesnt allow it
i can also custom parse
Quick fix: replace any space with "T"
i am not forced to do it
or use it
i can also provide my own format and it will parse it happily
Then just use the BNF grammar from the ISO spec 🙂
Thats why i originally asked for a "exact pattern.
Is it "%Y-%m-%d %H:%M:%S%.f%z"
Identifiers used are defined here: https://docs.rs/chrono/latest/chrono/format/strftime/index.html
"
chrono::format::strftime - Rust
strftime/strptime-inspired date and time formatting syntax.what is a bnf grammar
Well, the Z may be any timezone offset. In Postgres it won't give you Z it will give you
+00 or the offset of timezone set in the client
This part of the RFC:
yes
there is that problem
RFC 3999 says it can be either Z or the offset.
only replacing it with T will lead to problems i think
Hmmm
The rust docs says
%z will parse the offset, so it should work (I'm not a rust programmer, but it looks very similar to C's definition)already tried it
looks like you want
%#z
to allow missing minutesand i have 2 questions
are minutes represented as (i.e.) +0110 or +01:10
what if you just made postgres return the seconds since epoch? if rust can't handle that, i'd just give up 🙂
I have not tried half-hour timezones... let me try one. India is easy.
I am using the timestamptz to store the time a file was last changed
if you're working in UTC they're the same.
how would i change it anyways?
i havent seen any options in supabase to change it to my local timezone
cet
so I can ignore the minutes
You cannot change the core DB to any other timezone. However, when your client connects you can set that connection to whatever timezone you want.
i mean i would change the last modified column with a trigger
or wait i cant
i need to set it manuall y
its application specific
for example, here are two representations of the same timestamp from my database in different timezones:
If I present a string to the DB with the "+05:30" it will do the right thing when saving it.
i mean i am using rust for the timestamps and i can create them to be UTC
saving it to UTC
The DB doesn't care the timezone you specify like
update foo set mytimestamp='2024-01-16 01:17:51.7006+05:30' will be identical to set mytimestamp='2024-01-15 19:47:51.7006+00'
internally it will store the same instant in time.aight so that will not be a concern
when you query it, it check "what is the timezone of the client connected to me" and formats to that timezone. As you see in my prior example where I set the client timezone to Kolkata.
The defaults for everything is UTC because that's how Supabase set it up.
nice and the connections are UTC by default
lets assume this case right
this means in rust i'd have to use %:z
but in the %:z there is not the option for the minutes to be missing
you need the
%#z to make the minutes optional.but %#z is the same as %z and %z is +0930
interesting. they don't have a
%#:z variant that you want here 😦yes
tried both just now
they dont work
neither
if you don't explicitly set your timezone, Supabase will always return the timestamp formatted with
+00i think the best way would be to set the times myself (via rust) because i think when you set them with a different format and try to retrieve them theyll still remain in that format
just don't change your client timezone.
doesn't matter the timezone you specify on insert
this would work for my case but i plan on giving it to people
You could force your client to
set timezone='UTC' and then not worry about it.maybe but when i tested it and i put a different format (that is still ISO 8601 compliant) for a timestamptz and the downloaded that row as csv it showed the timestamptz in the format i specified it in
true but there is still the miliseconds problem lil
that doesn't make sense it would leave your original format. it always parses the timestamp to store in its own internal format.
and formats for output format.
are you looking at your CSV in Excel?
there's no difference there. the fractional seconds are zero so are omitted
no in vscode and nvim
just like all the extra zeros after the
47 in the first row.but i am talking about the case where there are actually fractional seconds
it will keep them.
cant define one format in rust and ship another from the db
up to some precision. i don't recall offhand what that precision is.
6 or some
then you have to use the
to_char() function to format the timestamps to your specification.yes
hmmm
can i encode the to_char to alway ship with UTC
but the input still doesn't matter as they are parsed and stored in the internal format.
regardless of the connection
let me try it
yeah youre right
maybe i mixed some files up and it seemed like it
it will automatically convert it
I am using the postgrest-rs api to communicate to the server
There's no way to change the client timezone with the postgrest interface.
for fun:
representation doesn't matter. it is all the same instant in time.
so wait i shouldnt worry about it right
every language can handle an epoch time integer, so that is always my go-to when I have to worry about parsing dates.
meaning i can ignore the + and everything that comes after it
i would document the heck out of that assumption, but it should hold.
why dont we all use it
because humans can't parse it.
and of course the 2038 problem 🙂
true
so maybe don't use it.
wait right
yes
back to to_char
👍
do you maybe know that
wait
I don't know off hand.
it doesnt matter anyways
more fun. Pg can deal with 2038+
too dangerous
yeah. i agree.
i am using the rest api
so i think i have to pack this up into a rpc
yes, you will have to.
and on insert i can use any iso8601 compliant format (or so i hope)
insert can parse a lot of arbitrary time-looking strings. but some may be ambiguous, so stick to the ISO format for best results.
thank you
i think i found a good solution
switch the rust time library
For anyone wondering why my solution was: I switched to the time create and replaced the space between date and time with a "T" and afterwards parsed it with
let date = OffsetDateTime::parse("2044-01-15T19:47:51.213424+01", &Iso8601::DEFAULT); Does the trick quite reliably