Support TIMESTAMP WITH TIMEZONE and without Y2K38 limit

Open
#20,522 4 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
5/5
Estimated time
Over a week
Newbie friendliness
25/100
Issue type
Feature
Clarity
Mostly clear
Activity status
Stale
Tech stack
mysql, sql
Domain
databases

Research direction

The issue names no files, tests, or entry points, so begin by locating TiDB's existing TIMESTAMP and DATETIME type handling and related tests. Done would require a defined, tested type supporting timezone-aware storage and presentation without the Y2K38 limit, including a clear compatibility and migration decision.

Written by the indexing model from the issue text.

Description

feature/accepted type/compatibility type/feature-request

Feature Request

Is your feature request related to a problem? Please describe:

This is forked from https://github.com/pingcap/tidb/issues/20348

Describe the feature you'd like:

MySQL has both TIMESTAMP and DATETIME datatypes, which have different behaviors regarding how to handle timezones. I prefer TIMESTAMP, but it has a limitation that the range of values is limited to 32-bit (i.e. it will not be able to handle dates past 2038).

The SQL standard has a type TIMESTAMP WITH TIMEZONE. My request is to create a new data type which:

  • Has timestamp like semantics (converts to UTC for storage)
  • Remembers the timezone that was set on storage (can be presented back on read)
  • Does not have a Y2K38 limitation.

Describe alternatives you've considered:

The alternative proposal would be to also fix TIMESTAMP to not have a Y2K38 limit, but this may cause compatibility issues with MySQL such as migrations back (although it could be protected via a flag).

Teachability, Documentation, Adoption, Migration Strategy:

It is fairly easy to teach because it is part of the SQL standard. Eventually TIMESTAMP will need to be improved, deprecated, fixed etc. As we get closer to Y2K38 storing as TIMESTAMP will become a similar issue to storing currency in FLOAT columns.

Dominant language
Go
Stars
40.6k
Forks
6.2k
Avg merge
3d 1h
Merged PRs (30d)
154

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from pingcap/tidb

All issues in pingcap/tidb

Similar issues

More Go issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.