drizzle-team / drizzle-team/drizzle-orm

[BUG]: Drizzle Studio does not query for TIMESTAMP with UTC for Planetscale/MySQL

Open
#2,286 0 comments 2 reactions 1 assignee Claimed by @RomanNabukhotnyi View on GitHub
bug db/mysql drizzle/studio
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.