在PostgreSQL数据库中保存日期和时间的最佳方法

Question

在PostgreSQL数据库中保存日期和时间的最佳方法

5

我将在我的PostgreSQL数据库中保存日期和时间，并以适当的格式获取并显示给用户。假设应用程序用户位于伊朗并使用波斯历日期/时间系统。在一年的前半段，伊朗时间是UTC+04:30，但在其余时间里，它是UTC+03:30。事实上，伊朗使用夏令时。重要提示：有时我们会决定将数据库服务器位置从伊朗更改为欧洲或其他地方。现在我有一些问题：

在保存日期和时间时，哪种数据类型更方便？TIMESTAMP (TIMESTAMP WITHOUT TIMEZONE) 还是 TIMESTAMP WITH TIME ZONE (TIMESTAMPZ)？
在保存仅时间时，哪种数据类型更方便？TIME 还是 TIME WITH TIME ZONE？
在保存仅日期时，哪种数据类型更方便？DATE (公历)、String (波斯历) 还是自定义数据类型？
如何为我们的数据库设置一次性的时区？我想为数据库设置时区一次，之后所有 TIMESTAMPZ 列上的查询都将在该时区内保存和获取，并且会考虑夏令时？
当我想要保存当前日期和时间时，最好的 SQL 查询语句是什么？

a- INSERT INTO test(d) VALUES(now());

b- INSERT INTO test(d) VALUES(now() at time zone 'utc');

c- INSERT INTO test(d) VALUES(now() at time zone 'Asia/Tehran');

d- INSERT INTO test(d) VALUES(current_timestamp);

e- INSERT INTO test(d) VALUES(now() at time zone 'utc');

f- INSERT INTO test(d) VALUES(now() at time zone 'Asia/Tehran');
5-c 和 5-f 在保存时是否考虑夏令时？
当数据库具有“亚洲/德黑兰”时区时，5-a 和 5-d 是否在“亚洲/德黑兰”中保存？
当我想从数据库查询时，哪个选项最适合我的情况？

a- SELECT d FROM test;

b- SELECT d at time zone 'utc' FROM test;

c- SELECT d at time zone 'Asia/Tehran' FROM test;
7-c 是否考虑夏令时？
当数据库具有“亚洲/德黑兰”时区时，7-a 是否考虑了“亚洲/德黑兰”的时区和夏令时？

如果我最好使用时间戳来保存日期/时间，那么我必须添加夏令时并将其转换为波斯历日期/时间并向用户显示，反之亦然。

- Pezhman Parsaee

2

时间戳与时区总是保存在UTC中 - 夏令时并不重要。但是Postgres会在输出时进行格式化，考虑夏令时等因素。因此，最好对所有三个时间戳使用timestamtz，并使用适当的格式选择完整日期、仅日期和仅时间。 - Vao Tsun

@VaoTsun 谢谢。所以如果我选择TIMESTAMPZ，当我们从数据库查询时它也会考虑夏令时吗？这会影响已经保存的数据吗？您能否回答第四个问题？（我现在添加第四个问题） - Pezhman Parsaee

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Vao Tsun · Accepted Answer

https://www.postgresql.org/docs/current/static/datatype-datetime.html

对于带有时区的时间戳（timestamp with time zone），内部存储的值始终为协调世界时（UTC，传统上称为格林威治标准时间GMT）。具有显式时区的输入值将使用该时区的适当偏移量转换为UTC。如果输入字符串中未指定时区，则假定其在系统的TimeZone参数指示的时区中，并使用时区偏移量将其转换为UTC。

输出带有时区的时间戳值时，它总是从UTC转换为当前时区，以该区域的本地时间显示。要查看另一个时区中的时间，请更改时区或使用AT TIME ZONE结构

timestamptz
timestamptz和仅选择日期
timestamptz和仅选择时间
无论您的语言环境或设置如何，数据库都会将具有时区意识的时间戳保存为UTC。时间戳始终通过调整TimeZone参数来转换为UTC。每次显示数据时都会发生相同的反向转换。服务器的TimeZone仅是默认值，如果客户端没有指定任何值，则使用该默认值。数据库TimeZone会覆盖postgresql.conf中指定数据库的时区设置，但是客户端设置将覆盖数据库设置。