用SQL写个表，专门存时间信息那种，咋整比较方便快速呢

要搞一个专门存时间信息的表,用SQL来做，想弄得方便又快速，其实核心就两点：一是把表结构设计得合理，二是根据你将来怎么用它来选择合适的字段类型，咱就围绕这两点，像聊天一样把它说清楚。

咱得想想,你到底要存些什么样的“时间信息”？这个特别重要，因为不同的用途，最好的存法是不一样的，我琢磨了一下，大概有这么几种常见情况：

第一种情况,你只是想记录一个瞬间，用户注册的准确时间”、“订单下单的时间”、“某条新闻发布的日期”，这种最简单，你就需要一个字段，能精确到某个点。

第二种情况,你不光要记录一个瞬间，还想知道这个数据是啥时候被创建到数据库里的，以及后来啥时候被修改过，这是一种非常常见且有用的设计，比如你想追踪一条记录的“生平”。

第三种情况,你要处理的是一个时间段，一个会议的起止时间”、“一个活动的报名周期”、“员工的有效在职期”，这时候你需要两个字段，一个表示开始，一个表示结束。

第四种情况,你可能需要记录更复杂的时间信息，每周三下午三点”、“每年的国庆节”，或者处理全球不同时区的时间。

想清楚你要干啥之后,咱就来聊聊SQL里那些用来存时间的家伙事儿，别看名目不少，但常用的就那几个，咱挑着说。

对于上面说的第一种情况,记录一个瞬间，你有几个主要选择：DATE、TIME、DATETIME、TIMESTAMP，它们区别在哪呢？DATE只管年月日，2023-10-27'。TIME只管时分秒，14:30:00'，这俩都好理解，关键是DATETIME和TIMESTAMP，它俩都能存年月日时分秒，2023-10-27 14:30:00'，那用哪个呢？这有个挺重要的区别：DATETIME存的就是你给它的那个时间，它不关心数据库服务器在哪个时区，而TIMESTAMP实际上存的是从1970年1月1日到现在经过的秒数（类似时间戳的概念），它和数据库服务器的时区是绑定的，当你存入一个时间，它会根据服务器时区转换成UTC时间存起来；当你取出来的时候，它又会根据服务器时区转换回本地时间显示，如果你的应用可能跨时区（比如服务器在国外，用户在国内），用TIMESTAMP会更方便，它能自动帮你做时区转换，但DATETIME的表示范围更大，在MySQL里，TIMESTAMP的范围是1970年到2038年某天（这就是著名的2038年问题），而DATETIME范围是1000年到9999年，如果你要存历史很久远的时间或者很未来的时间，就得用DATETIME，简单说，一般业务中，关心时区用TIMESTAMP，不关心或者需要大范围就用DATETIME。

咱就来动手建几个表,对应刚才说的那几种情况。

针对第一种情况（记录瞬间）的表结构例子： 假设是个用户表，记录注册时间。

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    -- 这里用DATETIME，假设我们不特别关心时区转换，而且要存的年份范围很广
    registration_time DATETIME NOT NULL
);

或者,如果你用的是PostgreSQL，它可能更推荐用TIMESTAMP WITH TIME ZONE（简称TIMESTAMPTZ）来更好地处理时间。

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    registration_time TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

注意看,这里我加了个DEFAULT CURRENT_TIMESTAMP，这是个好东西，意思是如果你插入数据时没指定这个字段，数据库会自动把当前时间填进去，这对于记录创建时间非常方便。

针对第二种情况（记录创建和更新时间）的表结构例子： 这是个超级实用的技巧，几乎每个表都可以这么干。

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,VARCHAR(200) NOT NULL,
    content TEXT,
    -- 创建时间，只在记录插入时设置一次，之后不变
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -- 更新时间，在记录插入和每次更新时都自动设置为当前时间
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

看updated_at那个字段，ON UPDATE CURRENT_TIMESTAMP是关键，有了它，每次你修改这条记录的其他字段时，这个updated_at字段都会自动更新为当前时间，你完全不用手动去管它，这在做数据追踪和同步时极其有用。

针对第三种情况（记录时间段）的表结构例子： 比如一个活动表。

CREATE TABLE events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    event_name VARCHAR(100) NOT NULL,
    -- 活动开始时间
    start_time DATETIME NOT NULL,
    -- 活动结束时间
    end_time DATETIME NOT NULL,
    -- 可以加个检查约束，确保结束时间晚于开始时间（如果数据库支持的话，比如MySQL 8.0+）
    CHECK (end_time > start_time)
);

有了开始和结束时间,你就能很容易地写查询找出“正在进行中的活动”（WHERE NOW() BETWEEN start_time AND end_time）或者“尚未开始的活动”等等。

针对第四种情况（复杂时间和时区）： 这个就稍微复杂点，对于重复性事件，可能需要在应用逻辑里处理，或者用额外的字段来描述规则（比如cron表达式），而对于时区，如果你用的DATETIME类型，但又需要知道具体时区，一个好办法是单独用一个VARCHAR字段来存储时区信息，Aisa/Shanghai'，然后在应用里根据这个字段和DATETIME值来正确解释时间，像PostgreSQL的TIMESTAMPTZ就内部处理了这些，更省心。

为了让你的表真的“快速”，记得在时间字段上建索引，如果你经常要按时间范围查数据（查询昨天一天下的订单”），在没有索引的情况下，数据库得把整张表翻个底朝天，这叫全表扫描，非常慢，给那个时间字段加上索引之后，数据库就能用高效的方式快速定位到你要的数据。

CREATE INDEX idx_registration_time ON users(registration_time);
CREATE INDEX idx_created_at ON articles(created_at);

索引就像书的目录,能让你不用一页一页翻就能找到内容。

方便快速的时间表,一是选对类型（DATE/DATETIME/TIMESTAMP按需选），二是巧用默认值（DEFAULT CURRENT_TIMESTAMP）和自动更新（ON UPDATE CURRENT_TIMESTAMP）减少手动操作，三是针对查询条件建索引提升速度，把这些结合起来，你的时间表就搞得八九不离十了。