极客笔记数据库中的时间概念
以关系形式，数据库被用来模拟现实世界外部某个部分的状态。一般来说，数据库系统只存储一个状态，即现实世界的当前状态，并且不保留之前和过去状态的数据，除非有时作为审计追踪。如果现实世界的当前状态变化，数据库会被编辑和更新，之前状态的信息将会丢失。
但是，在大多数实际应用中，存储和检索之前状态的信息是重要的。例如，学生数据库必须有关于学生过去表现历史的信息，以便准备最终结果。对于成功的操作，自主机器人系统必须保留关于环境中当前和历史传感器数据的知识。
以下是在数据库中使用时间的一些常见方法，以及处理与时间相关功能的示例和语法−
时间戳
时间戳用于记录特定记录创建或修改的日期和时间。在大多数数据库中，当插入或更新记录时，时间戳会自动生成。
在MySQL中创建带有时间戳列的表的语法−
CREATE TABLE example_table (
id INT PRIMARY KEY,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
输出表格
+------------+--------------------------------------+
| Column | Type |
+------------+--------------------------------------+
| id | INT |
| created_at | TIMESTAMP DEFAULT CURRENT_TIMESTAMP |
+------------+--------------------------------------+
| PRIMARY KEY| (id) |
+------------+--------------------------------------+
在数据库中插入一个带有当前时间戳的记录的示例−
INSERT INTO example_table (id) VALUES (1);
输出表格
+----+---------------------+
| id | created_at |
+----+---------------------+
| 1 | |
+----+---------------------+
基于时间分区
基于时间分区是通过根据时间将数据分割成更小、更易管理的块来提高处理大型数据集的性能。
在 Apache Cassandra 中，创建具有基于时间分区的表的语法如下 –
CREATE TABLE example_table (
id INT,
created_at TIMESTAMP,
value TEXT,
PRIMARY KEY ((id), created_at)
) WITH CLUSTERING ORDER BY (created_at DESC)
AND compaction = {'class': 'TimeWindowCompactionStrategy',
'compaction_window_size': '1',
'compaction_window_unit': 'DAYS'};
输出表
+------------+--------------+--------------------------------------------+
| Column | Type | Modifiers |
+------------+--------------+--------------------------------------------+
| id | INT | PRIMARY KEY |
| created_at | TIMESTAMP | PRIMARY KEY |
| value | TEXT | |
+------------+--------------+--------------------------------------------+
| CLUSTERING | ORDER BY | (created_at DESC) |
+------------+--------------+--------------------------------------------+
| compaction | TimeWindow | {'class': 'TimeWindowCompactionStrategy',
| | Strategy | 'compaction_window_size': '1',
| | | 'compaction_window_unit': 'DAYS'} |
+------------+--------------+--------------------------------------------+
这将创建一个表，其中的数据是按”id”进行分区和”created_at”进行聚集的。数据还会根据1天的时间窗口进行压缩。
时区
在处理记录在不同时区的数据时，正确地进行时区转换非常重要。
在PostgreSQL中，将时间戳转换为不同时区的语法如下 −
SELECT created_at AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York' FROM example_table;
将”created_at”时间戳转换为东部时区。
输出表格
+------------------------+
| timezone_adjusted_time |
+------------------------+
| 2023-03-18 08:00:00-04 |
| 2023-03-17 23:30:00-04 |
| 2023-03-17 15:15:00-04 |
| 2023-03-17 10:00:00-04 |
+------------------------+
历史数据
历史数据用于跟踪随时间变化的数据。在大多数数据库中，这是通过创建一个单独的表来存储对特定记录所做的更改历史来完成的。
在SQL Server中创建历史表的语法为−
CREATE TABLE example_table_history (
id INT,
created_at TIMESTAMP,
value TEXT,
changed_at TIMESTAMP,
changed_by TEXT
);
这将创建一个单独的表来跟踪对”example_table”表所做的更改。每当在”example_table”表中修改一条记录时，就会向”example_table_history”表中添加一条新记录，记录包括当前时间戳、进行更改的用户和记录的旧值。
让我们向表中插入一些值 –
INSERT INTO example_table_history (id, created_at, value, changed_at, changed_by)
VALUES (1, '2022-01-01 12:00:00', 'Value 1', '2022-01-02 12:00:00', 'User 1'),
(2, '2022-02-01 12:00:00', 'Value 2', '2022-02-02 12:00:00', 'User 2'),
(3, '2022-03-01 12:00:00', 'Value 3', '2022-03-02 12:00:00', 'User 3');
SELECT * FROM example_table_history;
输出表格
id | created_at | value | changed_at | changed_by
---|----------------------|---------|----------------------|------------
1 | 2022-01-01 12:00:00 | Value 1 | 2022-01-02 12:00:00 | User 1
2 | 2022-02-01 12:00:00 | Value 2 | 2022-02-02 12:00:00 | User 2
3 | 2022-03-01 12:00:00 | Value 3 | 2022-03-02 12:00:00 | User 3
结论
总之，时间是数据库中一个重要的概念，被用于许多不同的方式。时间戳用于记录特定记录创建或修改的日期和时间，而基于时间的分区用于在处理大型数据集时提高性能。在处理记录在不同时区的数据时，适当地转换时区是非常重要的。最后，历史数据用于跟踪随时间的变化，并通常存储在一个单独的表中。总体而言，理解时间在数据库中的不同使用方式对于构建健壮可扩展的应用程序是至关重要的。