Concept of Time in database

In the form of a relation, a database is used to mimic the status of some part of the real world outside. Database systems, in general, store just one state, which is the present state of the actual world, and do not keep data regarding prior and past states, except sometimes as audit trails. If the real world's current state changes, the database is edited and updated, and knowledge about the previous state is lost.

But, in the majority of real-world applications, it is important to store and retrieve information about previous states. A student database, for example, must have information about that student's past performance history in order to prepare the final result. For successful action, an autonomous robotic system must retain knowledge regarding current and historical sensor data from the environment.

Here are some common ways that time is used in databases, along with examples and syntax for working with time-related features −

Timestamps

Timestamps are used to record the date and time when a particular record was created or modified. In most databases, timestamps are automatically generated when a record is inserted or updated.

The syntax for creating a table with a timestamp column in MySQL −

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Output Table

+------------+--------------------------------------+
| Column     | Type                                 |
+------------+--------------------------------------+
| id         | INT                                  |
| created_at | TIMESTAMP DEFAULT CURRENT_TIMESTAMP  |
+------------+--------------------------------------+
| PRIMARY KEY| (id)                                 |
+------------+--------------------------------------+

Example of inserting a record with the current timestamp −

INSERT INTO example_table (id) VALUES (1);

Output Table

+----+---------------------+
| id | created_at          |
+----+---------------------+
|  1 |                     |
+----+---------------------+

Time-based partitioning

Time-based partitioning is used to improve performance when working with large datasets by splitting data into smaller, more manageable chunks based on time.

The syntax for creating a table with time-based partitioning in 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'};

Output Table

+------------+--------------+--------------------------------------------+
| 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'}          |
+------------+--------------+--------------------------------------------+

This creates a table where data is partitioned by "id" and clustered by "created_at". The data is also compacted based on a time window of 1 day.

Time zones

When working with data that is recorded in different time zones, it's important to properly convert between time zones as needed.

The syntax for converting a timestamp to a different time zone in PostgreSQL −

SELECT created_at AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York' FROM example_table;

This converts the "created_at" timestamp to the Eastern Time Zone.

Output Table

+------------------------+
| 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 |
+------------------------+

Historical data

Historical data is used to track changes over time. In most databases, this is done by creating a separate table that stores a history of changes made to a particular record.

The syntax for creating a history table in SQL Server −

CREATE TABLE example_table_history (
    id INT,
    created_at TIMESTAMP,
    value TEXT,
    changed_at TIMESTAMP,
    changed_by TEXT
);

This creates a separate table that tracks changes made to the "example_table" table. Whenever a record is modified in the "example_table" table, a new record is added to the "example_table_history" table with the current timestamp, the user who made the change, and the old value of the record.

Let’s insert some values into the table −

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;

Output Table

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

Conclusion

In conclusion, time is an important concept in databases and is used in many different ways. Timestamps are used to record the date and time when a particular record was created or modified, while time-based partitioning is used to improve performance when working with large datasets. When working with data that is recorded in different time zones, it's important to properly convert between time zones as needed. Finally, historical data is used to track changes over time and is typically stored in a separate table. Overall, understanding the different ways that time is used in databases is essential for building robust and scalable applications.