Parse `2007-12-23T10:15:30+01:00` while preserving offset

[Yogu]

I'm trying to migrate code using js-joda's OffsetDateTime. This is a type for a date-time with an offset but without a named time zone.

• It can be parsed with Temporal.Instant.from('2007-12-23T10:15:30+01:00'), but you lose the time zone offset by doing this
• It can be represented by Temporal.ZonedDateTime by duplicating / moving the offset into brackets: Temporal.ZonedDateTime.from('2007-12-23T10:15:30+01:00[+01:00]')

Is there a way to parse the original string (2007-12-23T10:15:30+01:00) into something where I can read out the offset, so I can display the time with the original time zone offset?

[justingrant]

Hi @yogu - Great question. If you just want the offset, you can use Temporal.TimeZone:

s = '2007-12-23T10:15:30+01:00';
offset = Temporal.TimeZone.from(s).id;
// => '+01:00'

The trick is that Temporal.TimeZone.from (or any Temporal API that accepts a time zone) will accept many different kinds of inputs:

• An IANA time zone name string like America/Los_Angeles
• An offset string like +01:00
• A valid ISO string containing an IANA time zone name in brackets (if there's also an offset string before the brackets, it's ignored)
• A valid ISO string containing an offset string in brackets
• A valid ISO string containing without anything in brackets, in which case the offset string is considered to be the time zone
• A Temporal.ZonedDateTime instance, in which case its time zone is used
• A Temporal.TimeZone instance
• An object with a timeZone property that's a string, a Temporal.ZonedDateTime instance, or a Temporal.TimeZone instance.

This makes it easy to use your original string anywhere you'd need the time zone. For example, if you want to round-trip back to the original string, you can use the timeZone option in Instant.toString:

s = '2007-12-23T10:15:30+01:00';
instant = Temporal.Instant.from('2007-12-23T10:15:30+01:00');
instant.toString({timeZone: s});
// => '2007-12-23T10:15:30+01:00'

Will this work for the use cases you're trying to support? Also, out of curiosity, what are those use cases? Specifically, is there any expectation of being able to created derived values from the OffsetDateTime, like adding 2 days or 2 hours, setting time to midnight or noon, or otherwise creating a new value based on the old one? Or are you just using it as a timestamp (and want to display that timestamp using its original offset) but you aren't planning to create any derived values from it?

I ask because creating derived values using an offset time zone will break around DST transitions, so I'm always curious to know how developers are using that Java type.

[ptomato]

Yes there is, although I agree it isn't very obvious.

If you are storing exact time stamps but just need to display them or serialize them with the original time zone offset, you can use the timeZone option of Temporal.Instant.prototype.toString() to do that. If you give an ISO string to Temporal.TimeZone.from() it'll create a time zone from the bracketed name if present, but if absent it'll create an offset time zone, so you can store the original offset as a TimeZone object, and do something like instant.toString({ timeZone }).

The other possibility (and I expect this is much, much rarer in practice) is that you have these strings with UTC offset that are semantically ZonedDateTime strings, but for the offset-only time zones used in maritime shipping. If that's the case, and you need a ZonedDateTime in one of these maritime time zones, for a string s, you can do Temporal.Instant.from(s).toZonedDateTimeISO(s) (letting toZonedDateTimeISO() implicitly call Temporal.TimeZone.from()` on the string.)

I hope that answers your question!

If you don't mind, I have a question for you in return — which of the above two scenarios best matches what you're doing? If it's the second one, can you tell a bit more about your use case?

[justingrant]

Haha @ptomato and I answered at the same time... even including the same grilling you about your use cases! :-)

[justingrant]

I think this question has been answered, so I'll close this issue now. Feel free to re-open if we missed something.

[felixfbecker]

It's a bit unfortunate that the final spec now of Temporal doesn't support this use case without jumping through hoops.

E.g. SQL doesn't have a representation of ZonedDateTime, it only has TIMESTAMP WITH TIME ZONE which only has an offset. Another example is Git, which stores offsets for commit dates, but no time zones. The closest we can get to represent these is:

const zdt = Temporal.Instant.from(value).toZonedDateTimeISO(Temporal.TimeZone.from(value))
// Round-trip:
zdt.toString({ timeZone: "never" })

Calling zdt.toString() without parameters, or including it in JSON.stringify() without a replacer, will return a weird string 2024-03-26T00:12:03.284337+02:00[+02:00] that is not understood by most systems otherwise understanding ISO8610 strings fine.

As the maintainer of a database driver for Node you're now faced with the decision: Do you choose Temporal.Instant as the default representations (vast majority of use cases, but loses data) or do you use Temporal.ZonedDateTime (no data loss, but the vast majority of users will have to learn the gotchas and always pass options to stringify).

It would have been more convenient to have ZonedDateTime accept a string without the bracketed time zone (without throwing) and to not include the bracketed time zone in toString() output if it's an offset. Or alternatively, have a dedicated Temporal.OffsetDateTime class. Maybe this can be considered for a future addition.

[justingrant]

There are valid use cases for an OffsetDateTime-like type, but could you explain why OffsetDateTime would be helpful for a DB driver working with TIMESTAMP WITH TIME ZONE database columns?

I ask because (I'm sure you know this but adding context for other less-experienced future readers) the equivalent of SQL's TIMESTAMP WITH TIME ZONE type is Temporal.Instant, because the database does not store the offset. It only uses the offset to determine the UTC timestamp before storing that UTC timestamp.

From the Postgres docs:

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). 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's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.4).

A further discussion of this is found in Jon Skeet's helpful answer here.

Therefore, it would be misleading for a DB driver to expose a Temporal.ZonedDateTime without first requiring the caller to specify the time zone. Instead, like in SQL, when showing TIMESTAMP WITH TIME ZONE data it's always up to the caller to decide what time zone should be used to determine the local date/time and offset of that value.

That said, I assume it would be a useful for a DB driver to provide ergonomic assistance for users who want to convert Instant-like TIMESTAMP WITH TIME ZONE values into Temporal.ZonedDateTime instances. For example, helper functions like these:

class TimestamptzColumn {
  . . .
  // convert to a Temporal.ZonedDateTime
  asZonedDateTime: tz => this.instant.toZonedDateTimeISO(tz),

  // Format a timestamptz column with an offset. Note that it
  // would be bad to encourage users to create ZDT instances
  // using an offset because these ZDT instances would not be
  // safe for DST-sensitive operations like adding one month.
  formatWithOffset: tzOrOffset => this.instant.toString({tineZone: tzOrOffset})
}

Feel free to follow up with more context about your use case in case I misunderstood!

[felixfbecker]

Wow, you're completely right. I never realized that TIMESTAMP WITH TIME ZONE actually always stores in UTC. That simplifies things a lot for that case and it should just become a Temporal.Instant.

The other example with Git commit times still stands, I guess, but that is a way less common use case than working with databases.