PostGreSQL:时间戳时区问题

这篇具有很好参考价值的文章主要介绍了PostGreSQL:时间戳时区问题。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

时间|日期类型

        PostGreSQL数据库内置的时间类型如下,注意到:内置的时间类型被分为了with time zone-带时区、without time zone-不带时区两种类型,

postgresql 时区,GIS技术,postgresql,数据库

   timetimestampinterval都可以接受一个可选的精度值 p(取值:0-6),这个精度值声明在秒域中小数点之后保留的位数。缺省情况下,在精度上没有明确的边界。

时间|日期类型可接受的输入格式

        在实际使用中,对于一个时间类型,我们通常很关心如何为此种类型的字段做插入、更新操作,那么就要考虑:PostGreSQL可以接受什么样的输入格式?对于PGSQL来讲,日期和时间的输入可以接受几乎任何合理的格式,包括 ISO 8601、SQL-兼容的、传统POSTGRES的和其他的形式。

        如下表所示,PG官方推荐使用ISO 8601格式的时间、日期字符串输入。

postgresql 时区,GIS技术,postgresql,数据库

postgresql 时区,GIS技术,postgresql,数据库

时间戳:输入格式|当前时间

时间戳输入格式

        相比上面的部分,本文更加关心与时间戳相关的内容。

        时间戳类型的有效输入:由一个日期和时间的串接组成,后面跟着一个可选的时区,因此,以下两种输入都是有效的(都遵循ISO 8601标准),

1999-01-08 04:05:06
1999-01-08 04:05:06 -8:00

        SQL标准通过“+”或者“-”符号的存在以及时间后面的时区偏移来区分timestamp without time zonetimestamp with time zone文字。因此,根据标准,

        ①TIMESTAMP '2004-10-19 10:23:54':是一个timestamp without time zone;

   ②TIMESTAMP '2004-10-19 10:23:54+02':是一个timestamp with time zone。

获取当前时间戳     

         我们先来获取一个当前时间戳,PGSQL提供了以下两种方式来获取,

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME(precision)
CURRENT_TIMESTAMP(precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME(precision)
LOCALTIMESTAMP(precision)

注意CURRENT前缀、LOCAL前缀函数之间的区别:

  CURRENT_TIMECURRENT_TIMESTAMP传递带有时区的值;

  LOCALTIMELOCALTIMESTAMP传递的值不带时区。

        接着看一下官方给的例子,

SELECT CURRENT_TIME;
结果:14:39:53.662522-05

SELECT CURRENT_DATE;
结果:2001-12-23

SELECT CURRENT_TIMESTAMP;
结果:2001-12-23 14:39:53.662522-05

SELECT CURRENT_TIMESTAMP(2);
结果:2001-12-23 14:39:53.66-05

SELECT LOCALTIMESTAMP;
结果:2001-12-23 14:39:53.662522

        以2023-09-09 11:16:56.745139+08为例,表示:2023年09月09日,上午11时16分56.745139秒,+08东八区。

时间|日期输出格式

        时间|日期格式化也是我们做项目开发、数据库操作避不开的一个问题,PostGreSQL的时间/日期类型的输出格式可以设成四种风格之一: ISO 8601、SQL(Ingres)、传统的POSTGRES(Unix的date格式)或 German 。缺省是ISO格式。

        以下为4种格式的例子,

postgresql 时区,GIS技术,postgresql,数据库

        根据PG官方描述,实现数据格式化操作的方式有两种,

①日期/时间风格可以由用户使用SET datestyle命令选取,在postgresql.conf配置文件里的参数DateStyle设置或者在服务器或客户端的PGDATESTYLE环境变量里设置。

②格式化函数to_char(见第 9.8 节)也可以作为一个更灵活的方式来格式化日期/时间输出。

        我们来了解一下第②种。

数据类型格式化函数

        PostgreSQL格式化函数提供一套强大的工具用于把各种数据类型 (日期/时间、整数、浮点、数字) 转换成格式化的字符串以及反过来从格式化的字符串转换成 指定的数据类型。

postgresql 时区,GIS技术,postgresql,数据库

        可以看到:通过格式化函数,可以实现时间戳和字符串之间的互转操作,而对于格式化字符串的format控制参数模板串,可参见:9.8. 数据类型格式化函数 (postgres.cn),表9.25.

        例如:我们要将当前时间戳转换为类似于:yyyy-MM-dd hh:mm:ss这样的字符串格式(24小时制),那么SQL语句为:


SELECT to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')

--输出
2023-09-09 11:29:02

        接着,我们再将上面的输出字符串转换为时间戳,

SELECT to_timestamp('2023-09-09 11:29:02', 'YYYY-MM-DD HH24:MI:SS')

--
2023-09-09 11:29:02+08

关于时区

        PG官方对于时区的问题解释如下,并不建议使用带时区的类型,

PostgreSQL努力在典型使用中与SQL标准的定义相兼容。但SQL标准在日期和时间类型和功能上有一些奇怪的混淆。两个显而易见的问题是:

  • 尽管date类型与时区没有联系,而time类型却可以有。 然而,现实世界的时区只有在与时间和日期都关联时才有意义, 因为偏移(时差)可能因为实行类似夏时制这样的制度而在一年里有所变化。

  • 缺省的时区会指定一个到UTC的数字常量偏移(时差)

我们建议在使用时区的时候,使用那些同时包含日期和时间的日期/时间类型。我们建议使用类型 time with time zone (尽管PostgreSQL出于遗留应用以及与SQL标准兼容性的考虑支持这个类型)。 PostgreSQL假设你用于任何类型的本地时区都只包含日期或时间。

        关于UTC和ISO 8601的概念以及作用,可参考:时间标准基础知识UTC和ISO8601。

        查看数据库当前使用的时区,


show time zone
--Asia/Shanghai

        查看数据库可供选择的时区:

select * from pg_timezone_names;

postgresql 时区,GIS技术,postgresql,数据库

        设置时区,

①临时设置:退出cmd终端之后,时区设置就会丢失。

set time zone "Asia/Shanghai"

②永久设置:修改配置文件,

        如果想永久修改,我们需要更改配置文件postgresql.conf,将timezone进行修改,

log_timezone = 'PRC' timezone = 'PRC'

        将配置文件的这两个变量的值设置成自己想要的时区(PRC指:People's Republic of China),然后重新加载即可:pg_ctl reload。文章来源地址https://www.toymoban.com/news/detail-758455.html

到了这里,关于PostGreSQL:时间戳时区问题的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • postgresql数据脱敏技术介绍以及使用字符替换数据库脱敏示例代码

    在 PostgreSQL 数据库中实现数据脱敏(Data Masking)可以帮助保护敏感数据的隐私和安全。数据脱敏是通过修改或替换敏感数据的方法来隐藏或模糊数据的真实值,以防止未经授权的访问者获取敏感信息。 以下是一些常见的 PostgreSQL 数据库脱敏技术: 数据加密:使用加密算法对

    2024年02月16日
    浏览(48)
  • postgresql导入导出数据库的一些问题

    就创建一个空库,啥也不用管,也不用添加 postgis 之类的扩展 如果是不同节点之间迁移,建议先创建之前的用户,如果没有之前的用户导入时会导致表所有者是postgres

    2024年02月17日
    浏览(43)
  • 【PostgreSQL 数据库技术峰会(成都站)】云原生虚拟数仓 PieCloudDB Database 的架构和关键模块实现...

    2023年6月17日,中国开源软件推进联盟 PostgreSQL 分会在成都举办了数据库技术峰会。此次峰会以“新机遇、新态势、新发展”为主题,结合当下信创热潮、人工智能等产业变革背景,探讨 PostgreSQL 数据库在这些新机遇下的发展前景。峰会邀请众多行业大咖、学术精英、技术专家

    2024年02月11日
    浏览(49)
  • POSTGRESQL 关于2023-08-14 数据库自动启动文章中使用KILL 来进行配置RELOAD的问题解释...

    开头还是介绍一下群,如果感兴趣Polardb ,mongodb ,MySQL ,Postgresql ,redis ,SQL SERVER ,ORACLE,Oceanbase 等有问题,有需求都可以加群群内有各大数据库行业大咖,CTO,可以解决你的问题。加群请加 liuaustin3微信号 ,在新加的朋友会分到3群(共1170人左右 1 + 2 + 3) 首先道歉,昨天关于P

    2024年02月12日
    浏览(51)
  • PostgreSQL Linux操作PostgreSQL数据库

    PostgreSQL教程 菜鸟教程:https://www.runoob.com/postgresql/postgresql-tutorial.html 登录PG数据库:psql -U 用户名(U需要大写) 登录PG数据库(指定主机、端口,并进入指定数据库): psql -U 用户名 -h 127.0.0.1 -p 5432 -d 数据库名 -U 登录的用户名 -h 连接的主机(默认127.0.0.1,可替换成远程主机

    2024年02月11日
    浏览(64)
  • PostgreSQL时间相差天数

    执行SQL: 执行结果:

    2024年02月06日
    浏览(43)
  • PostgreSQL时间加减

    语法: 示例:  时间单位:  millennium century decade year quarter month week day hour minute second (sec) milliseconds microseconds

    2024年02月16日
    浏览(40)
  • 3.postgresql--时间函数

    select generate_series(3,5); select generate_series(3,5),generate_series(4,6) 步长一样 当前日期划分为24小时

    2024年02月16日
    浏览(32)
  • PostgreSQL构建时间

    – PostgreSQL构建时间

    2024年02月14日
    浏览(34)
  • PostgreSql 日期时间输出风格及顺序

      PostgreSQL 中日期时间的输出格式及顺序均由 datestyle 参数控制,两者可以被独立设置或者一起设置。默认值是 ISO,MDY。   PostgreSQL 中时间/日期类型的输出格式可以设成四种风格之一:ISO 8601、SQL(Ingres)、传统的 POSTGRES(Unix的date格式)或 German 。缺省是 ISO 格式(ISO标准

    2024年02月13日
    浏览(35)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包