astro:db: TimeStamps stored using NOW (in UTC) are being translated to their Local Time value making the JavaScript value of the Date object inaccurate

[timsexperiments]

Astro Info

Astro                    v4.7.1
Node                     v22.0.0
System                   macOS (arm64)
Package Manager          pnpm
Output                   server
Adapter                  @astrojs/cloudflare
Integrations             astro:db
                         @astrojs/db/file-url
                         @astrojs/tailwind

If this issue only occurs in one browser, which browser is a problem?

No response

Describe the Bug

Date values created with the NOW constant from astro:db (LibSQL value CURRENT_TIMESTAMP) are being treated as the local date value from the tables that use Date columns. Because the timestamp in the database is not stored with any timestamp info, the date will be treated as the local time of the client parsing the value.

What's the expected result?

Personally I would expect one of two things:

Option 1: I would expect the date to be stored in whatever timezone it was created in (I think this might be impossible due to the way that they are stored - but I am no SQLite expert so I could be wrong)

Option 2: The date should be returned as a string (not parsed by astro, similar to the example when using the code db.select({ date: NOW })...), and the user should be responsible for adding the correct timestamp.

Link to Minimal Reproducible Example

When running the reproduction you must use --remote for the code to work (otherwise you would need to replace line 13 of the src/index.astro file with the code rows: [{ date, localTime }], due to the way that the query is parsed on remote vs local / from file (will open a separate bug for this issue).

https://github.com/timsexperiments/astro-db-now-reproduction

Deployed Version: https://astro-db-now-reproduction.pages.dev/

Participation

• I am willing to submit a pull request for this issue.

[matthewp]

Yeah the fact that it is stored without the timezone is a bit of a problem. Going to discuss internally about what we should do about this, thanks for bringing it to our attention.