SpacetimeDB icon indicating copy to clipboard operation
SpacetimeDB copied to clipboard

Make printed output of `Timestamp` objects in all client SDK langs compatible with SQL queries

Open gefjon opened this issue 9 months ago • 2 comments

As in, it should be possible in a client to write:

ctx.subscription_builder()
  .subscribe(format!(
    "SELECT * FROM message WHERE sent >= {:?}",
    Timestamp::now(),
  ))
  .unwrap();

or the equivalent in C# or TypeScript, and wind up with a valid SQL query that SpacetimeDB will evaluate.

Currently, Timestamps print as raw us since the Unix epoch in Rust, and probably also in C#. I have no clue how printing objects works in TypeScript. OTOH, we parse them according to RFC 3339, which IIRC is roughly YYYY-MM-DDTHH:MM or whatever.

We have two obvious paths to unify this:

  1. Change Timestamp in Rust, C# and TypeScript to print as RFC 3339. In Rust, the time crate seems to do this. Presumably C# and TypeScript will also give us ways to make it happen with relatively little investment of engineer-hours. Note that we can almost certainly get away with unconditionally printing as UTC, or local time, or whatever's easiest, even if we're inconsistent between impls.
  2. Extend our SQL parser to accept a raw integer literal for Timestamps and treat it as a number of us since the Unix epoch. I am unsure how much effort this would be, as I don't know to what extent we control our SQL parser.

gefjon avatar Mar 12 '25 22:03 gefjon

Marking as a P1, at least initially, as my memory is that BitCraft wants to do this in their chat system. If they don't, we can punt this until some other customer asks.

gefjon avatar Mar 12 '25 22:03 gefjon

I'd prefer a 3rd more explicit option which is to add something akin to Timestamp::to_rfc3339.

joshua-spacetime avatar Mar 12 '25 23:03 joshua-spacetime

Worth noting: currently the parser accepts NO representation of a timestamp, even the ISO string one.

I fixed this in #2842.

egormanga avatar Jun 07 '25 01:06 egormanga