返回提示词目录第 1148 页

设计日期时间存储与迁移方案

按字段语义选择日期时间存储类型,制定时区读写规则、旧数据迁移步骤与跨时区边界测试。

完整提示词

<context>
Most date and time bugs come from storing every value the same way. There are at least four different kinds of value. An instant (an order was placed, a row was updated) is a point on the global timeline and belongs in a time-zone-aware type, normalised to UTC. A future local event (a meeting at 09:00 in Berlin next March, a shop opening hour) must be stored as local date and time plus an IANA zone name such as Europe/Berlin, because the offset for that date can change before it arrives; storing it as UTC breaks it when daylight saving rules or the user's zone change. A plain date (a birthday, an invoice date, a holiday) has no time zone at all, and storing it as midnight UTC shifts it by a day for half the world. A duration or interval is not a timestamp. Further traps: database types whose names mislead (a "timestamp" without zone that drivers silently convert using the server zone), offsets stored without zone names, epoch values in mixed seconds and milliseconds, and "day" boundaries in reports that must follow a business zone rather than UTC.

<fields>
{{fields}}
</fields>
{{#database}}Database and writers: {{database}}{{/database}}
{{#symptoms}}
<symptoms>
{{symptoms}}
</symptoms>
{{/symptoms}}
</context>

<task>
1. Classify every field as instant, future local event, plain date, local time of day, or duration, with one line explaining the meaning that decides it. Ask when the meaning is ambiguous.
2. Recommend a storage type for each in the stated database, plus any companion column (zone name, original offset for display). If the database is not given, describe the type by behaviour and give examples for two common databases.
3. Write rules for writing and reading: normalise instants at the boundary, session and server time zone settings, driver behaviour to check, how the API serialises each kind, and how reports pick the zone that defines a day.
4. Plan fixing existing data: how to find out which zone each existing value was actually written in (sampling, known write paths, comparison with logs), the conversion for each case, ambiguous and non-existent local times around clock changes, and a safe rollout that keeps readers working during the change.
5. List tests to add: daylight saving transitions, a user in a different zone from the server, dates near midnight, leap days, and recurring events across a rule change.
6. Open questions for product and data owners.
</task>

<constraints>
- Do not guess the zone that existing data was written in. Give the evidence to collect and treat it as unknown until checked.
- Do not state database type behaviour you are unsure of for a specific version; mark it "verify in your version's documentation".
- Never recommend storing future local events only as UTC, or plain dates as timestamps.
- Any migration of existing values must be reversible or keep the original column until verified.
{{> output/uncertainty}}
</constraints>

<output_format>
## Field classification
Table: Field | Kind | Why.
## Recommended types
Table: Field | Type | Companion column | Notes.
## Write and read rules
Bullets.
## Fixing existing data
Numbered steps, including the evidence to collect first and example SQL marked as untested.
## Tests to add
Checklist.
## Open questions
Numbered.
</output_format>

可替换内容

  • {{fields}}
  • {{#database}}
  • {{database}}
  • {{/database}}
  • {{#symptoms}}
  • {{symptoms}}
  • {{/symptoms}}
  • {{> output/uncertainty}}

分类与标签

编程开发、数据库设计、时区处理、日期时间存储、数据迁移、夏令时测试

前往 AI 图片创作