timestamp without time zone

时间戳(timestamp) 是一种计算机数据类型，表示一个时间点或某个时间段的起始点。而无时区的时间戳(timestamp without time zone) 则表示的是相对于UTC时间的时间差值，不考虑时区偏移量。

相关问题

TIMESTAMP WITHOUT TIME ZONE

A TIMESTAMP WITHOUT TIME ZONE is a data type in a database that stores a specific point in time, down to the second, without any reference to a particular time zone. This means that the timestamp is universal and does not change based on the location or time zone of the user or the server.

For example, if a user enters a timestamp of "2021-09-22 14:30:00" into a database with a TIMESTAMP WITHOUT TIME ZONE data type, it will be stored as it is. If another user in a different time zone accesses the same timestamp, it will still be displayed as "2021-09-22 14:30:00" without any conversion to their local time zone.

It is important to note that a TIMESTAMP WITHOUT TIME ZONE should not be confused with a TIMESTAMP WITH TIME ZONE, which includes an offset to indicate the time zone.

time with time zone 如何转成 timestamp without time zone

可以使用 PostgreSQL 中的 `AT TIME ZONE` 和 `TO_TIMESTAMP` 函数将 `time with time zone` 转换为 `timestamp without time zone`。具体步骤如下：

假设你有一个名为 `my_table` 的表，其中有一个名为 `my_column` 的 `time with time zone` 类型的列。你可以使用以下 SQL 语句将其转换为 `timestamp without time zone` 类型：

SELECT TO_TIMESTAMP(my_column AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS') FROM my_table;

在上面的 SQL 语句中，我们首先使用 `AT TIME ZONE` 将 `my_column` 转换为 UTC 时区的时间，然后使用 `TO_TIMESTAMP` 将其转换为 `timestamp without time zone` 类型。

如果你需要将 `timestamp without time zone` 转换为 `time with time zone`，可以使用相反的过程：

SELECT my_column AT TIME ZONE 'UTC' FROM my_table;

在上面的 SQL 语句中，我们首先将 `my_column` 转换为 UTC 时区的时间，然后使用 `AT TIME ZONE` 将其转换为 `time with time zone` 类型。