I'm not intricately familiar with how PostgreSQL represents time, but it seems like you can avoid going through aDateTime here and just compute aJiffTimestamp directly.

Addressed inf00d208

Is it correct to assume UTC here? There is no time zone information in PostgreSQL?

I wonder if it makes sense to leave out the impl forZoned and instead require that callers just use ajiff::Timestamp and do their own conversions toZoned as needed.

UTC is correct. From thePostgres docs:

Fortimestamp with time zone, the internally stored value is always in UTC... An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system'sTimeZone parameter, and is converted to UTC using the offset for the timezone zone.

Consumers will usually reach forTimestamp, butZoned beingToSql andFromSql can occasionally be convenient. I am in favour of including them. It would also matchchrono::DateTime<FixedOffset> andtime::OffsetDateTime having them.

I'm skeptical that aZoned implementation makes sense here personally. Think of aZoned as "an assertion that a particular datetime is in a specific geographic region." Just because youcan pick some kind of default here doesn't mean you should. And the civil datetime types are also somewhat questionable given that you're freely going to and from instants in time and back to inexact time. That also seems wrong.

I understand you're starting from a position of "this is how the Chrono integration works," but Chrono'sDateTime<FixedOffset> and time'sOffsetDateTime are much closer to Jiff'sTimestamp than Jiff'sZoned. The closest analog Chrono has to a JiffZoned isDateTime<chrono_tz::Tz>.

The only trait implementation I feel 100% confident about here is forjiff::Timestamp.

I have always felt weird about how Postgres handles time zones and how thetimestamp with time zone type does not in fact contain the time zone information. Keeping the actual geographic region requires an additional column. What you wrote aboutZoned resonates with me when it comes to correctness. I agree thatjiff::Timestamp matches up withtimestamptz andZoned does not match with any current Postgres datetime type. I was concerned about conforming to Postgres and this crate's implementation details but I agree with you now on omitting impl forZoned.

I am not sure I agree with your civil type concerns though. I feel like the Postgres types match up quite nicely for these inexact expressions of time

(Postgres also has atimetz. Can we express that in Jiff?)

REZoned: okay, great. Thank you.

I am not sure I agree with your civil type concerns though. I feel like the Postgres types match up quite nicely for these inexact expressions of time

I'm going based on the code in this PR. What I see is that a PostgreSQL timestamp (which is exact time) is being convert to and from a Jiffcivil::DateTime, which is inexact time. Going from inexact time to exact timeshould require some assertion of time zone. Indeed, if you add a time zone to acivil::DateTime, then you get aZoned. The same reason that aZoned impl is inappropriate applies here.

AIUI, PostgreSQL does haveDate andTime types, and those are inexact times, just like Jiff'scivil::Date andcivil::Time types.

Now that I look closer at this patch, I see that aDateTime is being constructed from a timestamp, whileDate andTime are being constructed from PostgreSQLdate andtime types. The fact that these are different is 100% certainly wrong. I don't think PostgreSQL has adatetime type (something that combinesdate andtime), so probably thejiff::civil::DateTime implementation should be removed.

Basically, any time you move between exact and inexact time, you should have some kind of time zone assertion involved. Andusually, "just use UTC" is not the right thing to do.

(Postgres also has a timetz. Can we express that in Jiff?)

Noooooooooo. See:https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_timetz

timestamp in Postgres isinexact, being a combination ofdate andtime without time zone. It can be used to record the general idea of the 2025 New Year's Countdown, taking place on Dec 31, 2024 at 11:59pm, without regard for the geographic location.

timestamptz is exact., asserting the time zone (albeit in UTC only).

timestamp in Postgres is inexact, being a combination of date and time without time zone.

Whoa. OK. Let me backup and read their docs more carefully... Because if atimestamp is just inexact time andtimestamptz is just inexact time with a time zone, then that doesnot make atimestamptz exact. It could be ambiguous. Butaccording to this, yes, PostgreSQL is saying that it is exact. So maybe they are doing disambiguation on insert or something. And also, this seems to indeed agree that just atimestamp is actually inexact time:

timestamp (also known as timestamp without time zone) doesn't do any of that, it just stores a date and time you give it. You can think of it being a picture of a calendar and a clock rather than a point in time. Without additional information - the timezone - you don't know what time it records.

Wild.

I had assumed thattimestamp was, well, just a Unix timestamp. Which is always in UTC. Andtimestamptz stored some extra bit of information like an offset. OK, so I think this is the mapping then:

• date ->jiff::civil::Date
• time ->jiff::civil::Time
• timestamp ->jiff::civil::DateTime
• timestamptz ->jiff::Timestamp

I think where I got confused is that in this patch, thetimestamp -> jiff::civil::DateTime is implemented by using what appears to be a representation for exact time (number of seconds since an epoch). But I guess that's just a representational thing.

And yes, it looks like the above mapping lines up with what you have in this PR. OK, sorry for the noise!

I had assumed thattimestamp was, well, just a Unix timestamp. Which is always in UTC. Andtimestamptz stored some extra bit of information like an offset. OK, so I think this is the mapping then:

• date ->jiff::civil::Date
• time ->jiff::civil::Time
• timestamp ->jiff::civil::DateTime
• timestamptz ->jiff::Timestamp

I think where I got confused is that in this patch, thetimestamp -> jiff::civil::DateTime is implemented by using what appears to be a representation for exact time (number of seconds since an epoch). But I guess that's just a representational thing.

It's certainly confusing.

timestamp andtimestamptz both internally store microseconds since Y2K midnight UTC. The difference is thattimestamptz converts the input to UTC before storing the number of microseconds. It bakes in the offset so we get the correct exact time, but the actual source time zone/offset is discarded.

For the record,DateTime andTimestamp are much better names thantimestamp andtimestamptz to describe what they really are.

And yes, it looks like the above mapping lines up with what you have in this PR. OK, sorry for the noise!

Comments were much appreciated :) At least we're only dealing with Terran time.