drizzle-team / drizzle-team/drizzle-orm
[BUG]: Drizzle Studio does not query for TIMESTAMP with UTC for Planetscale/MySQL
- Dominant language
- TypeScript
- Stars
- 35.8k
- Forks
- 1.6k
- Avg merge
- 2d 7h
- Merged PRs (30d)
- 4
Description
### What version of `drizzle-orm` are you using?
0.29.5
### What version of `drizzle-kit` are you using?
0.20.17
### Describe the Bug
Drizzle Studio does not display `TIMESTAMP` data with UTC time when using MySQL/Planetscale and as a result returns the wrong data. Seems like the UTC offsets are applied against the server timezone's offset.
### Expected behavior
When querying for a `TIMESTAMP`-type field in MySQL, default timezone returned is the server's.
From [PlanetScale docs](https://planetscale.com/blog/datetimes-vs-timestamps-in-mysql):
> hopefully your server and connection timezones are always set to UTC, but if they aren't, you might be in for a big surprise when the values you put in the database are not the values you get back out.
Drizzle's [Postgres docs on `TIMESTAMP`](https://orm.drizzle.team/docs/column-types/pg) clarify that
> For timestamp with time zone, the internally stored value is always in UTC
but the MySQL docs make no such assurance.
**It does appear that Drizzle returns values in UTC in my application...but not in Drizzle Studio!**
Drizzle Studio doesn't appear to be using my laptop's timezone. It's pretty hard to understand and I was really confused until I realized Drizzle Studio did not reflect the time zones in the application (which were correct after all). I also use the [PlanetScale CLI](https://planetscale.com/features/cli), which seems to be using UTC.
I would expect Drizzle Studio to return the same kinds of values that are returned in a Drizzle ORM application. In the case of these time zones, there IS a right answer and Drizzle Studio is using the wrong timezone.
I understand that it's possible to use `CONVERT_TZ(timestamp_column, @@session.time_zone, '+00:00')` in a query or even `SET SESSION time_zone = '+00:00';` but the former happens to have no effect on my query and it's not clear how to run the latter.
### Environment & setup
Drizzle Studio.
Contributor guide
Assessment
This issue has not been assessed yet.