PartiQL Datetime RFC and partiql_value::DateTime

[rajivr]

I am currently investigation how to design "sort keys" for datatime types.

In the PartiQL Datetime RFC, following is mentioned.

PartiQL extends the SQL date time string format to support RFC 3339 format. That is 1) PartiQL SHALL support use of T as date time delimiter and use of Z as Zulu time zone. 2) PartiQL SHALL support Unknown Time Zone (-00:00).

RFC 3339: If the time in UTC is known, but the offset to local time is unknown, this can be represented with an offset of "-00:00". This differs semantically from an offset of "Z" or "+00:00", which imply that UTC is the preferred reference point for the specified time.

I am guessing this requirement stems from a similar requirement from Ion Timestamps.

Currently partiql_value::DateTime is defined as follows.

pub enum DateTime {
    Date(time::Date),
    Time(time::Time),
    TimeWithTz(time::Time, time::UtcOffset),
    Timestamp(time::PrimitiveDateTime),
    TimestampWithTz(time::OffsetDateTime),
}

Wouldn't we need to be able to capture information regarding "UTC is known, but the offset to local time is unknown" information within DateTime type?

Maybe define DateTime type something like

pub enum DateTime {
    Date(time::Date),
    Time(time::Time),
    TimeWithTz(time::Time, Option<time::UtcOffset>),
    Timestamp(time::PrimitiveDateTime),
    TimestampWithTz(time::PrimitiveDateTime, Option<time::UtcOffset>),
}

In this case the None variant would indicate that the time is UTC, but offset is unknown.

Something else that I noticed is a minor inconsistency in the API design.

• partiql_value::DateTime::from_hms_nano_tz has tz_hours: Option<i8> and tz_minutes: Option<i8> as its parameters.

• partiql_value::DateTime::from_ymdhms_nano_offset_minutes has offset: Option<i32>.

Do you think it might make sense to harmonize how offsets are passed to DateTime constructors?

[rajivr]

@alancai98 I was wondering if you or anybody from PartiQL team had any comments on this?

I am specifically interested in learning about how "UTC is known, but the offset to local time is unknown" would be handled within PartiQL?

Btw, I do not really have an issue if PartiQL team decides "-00:00" won't be handled as part of PartiQL value, and only the parsing capability would be provided to a TZ variant.

[alancai98]

Hey @rajivr, sorry for the delay. I'll bring this discussion up w/ @yliuuuu who is more knowledgeable on all things date/time/timestamp related. We should be able to get back to you soon.

[yliuuuu]

Hi, @rajivr . Thanks for going through the RFC.

I am guessing this requirement stems from a similar requirement from Ion Timestamps.

This is correct. The RFC attempts for PartiQL TIMESTAMP type to unify the Ion Timestamp and Timestamp defined in SQL.

While Alan @alancai98 will have more information on Rust Implementation's decision and timeline on Timestamp implementation, I can provide more context on the original intension of the RFC.

So the RFC intended to preserve the unknown timezone property. The kotlin implementation has defined a dedicated object to hold this information.

how to design "sort keys" for datatime types.

The high level overview for datetime comparison are: If they represent the some point in time chronologically, they are considered equivalent.

My mental model to build is to convert them to UTC time first, then make the comparison.

So:
TIMESTAMP '2023-06-01 00:00:00+00:00' = TIMESTAMP '2023-05-31 17:00:00-07:00'

Now for unknown timezone:

If the time in UTC is known, but the offset to local time is unknown, this can be represented with an offset of "-00:00"

Consider: `TIMESTAMP '2023-06-01 00:00:00-00:00'

This is to say that I know the time in UTC is 2023-06-01 00:00:00, but I don't know in which time zone do the event occur.

So if we have TIMESTAMP '2023-06-01 00:00:00+00:00' = TIMESTAMP '2023-06-01 00:00:00-00:00'

We attempts to convert them to UTC time zone:

the left hand side is already in UTC time zone, so the UTC time is 2023-06-01 00:00:00

for the right hand side, even though I don't know the local offset, I know that the UTC time is 2023-06-01 00:00:00.

Given that the two UTC time are representing the same point in time, the two should be considered as equal.

The kotlin implementation has a comparator implemented, you can take a look if you are interested.

Let me know if this helps. If not, feel free to continue the discussion here.

[rajivr]

Hi @yliuuuu Thanks for detailed reply and references to Kolin code.

While I am not that familiar with Kotlin language, looking at some of the tests, and your pointer to comparator implementation, it looks to me

1. In-memory representation of datetime types with timezones in PartiQL is using "local time", and not UTC.
2. Conversion to UTC happens only when necessary for example in the comparator implementation.

This would be in contrast to something like PostgreSQL where

All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client.

Am I understanding this correctly?

While Alan @alancai98 will have more information on Rust Implementation's decision and timeline on Timestamp implementation

@alancai98 While having UDF support is still a high priority requirement for us, it would be nice if UnknownTimeZone semantics can be added to the Rust implementation as well.

[jpschorr]

@yliuuuu Did any of the edge case tests for this make their way into the conformance tests?

[yliuuuu]

@jpschorr PRs were created a while ago, but was waiting for approval of the RFC to them to be merged in conformance tests. Let me know if you prefers us to merge those tests first.

[yliuuuu]

@rajivr

I have not yet looked into PostgreSQL's implementation, so I can not say for sure.

But based on this experiment. It appears that PostgreSQL does lose the original offset information.

Considering:

CREATE TABLE test (
  t Timestamptz
);
INSERT INTO test (t) VALUES (TIMESTAMPTZ '2024-02-07 10:00:00-02:00');

SET TIME ZONE '+08:00';

SELECT 
     CURRENT_TIME,  // session time zone dependent
    TIMESTAMPTZ '2024-02-07 10:00:00-02:00',  // show as UTC
    t  // show as UTC
FROM test;

PartiQL preserves the original offset information is because SQL Spec defines an extract function that can extract the timezone information.

Consider PostgreSQL's behavior here.

# Postgresql
SET TIME ZONE '+08:00';

SELECT 
   EXTRACT(timezone_hour FROM CURRENT_TIME),  # -8
   EXTRACT(timezone_hour FROM TIMESTAMPTZ '2024-02-07 10:00:00-02:00'), # -8
   EXTRACT(timezone_hour FROM t) # -8
FROM test;

# PartiQL
SET TIME ZONE '+08:00';

SELECT 
   EXTRACT(timezone_hour FROM CURRENT_TIME),  # -8
   EXTRACT(timezone_hour FROM TIMESTAMPTZ '2024-02-07 10:00:00-02:00'), # -2
   EXTRACT(timezone_hour FROM t) # -2
FROM test;

Let me know if this make sense to you.

[rajivr]

Yes, it does. Thanks for creating this snippet.

[jpschorr]

@rajivr

I added an issue to PartiQL rust: partiql/partiql-lang-rust#444