• PostGreSQL:时间戳时区问题


    时间|日期类型

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

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

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

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

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

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

    时间戳输入格式

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

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

    1. 1999-01-08 04:05:06
    2. 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提供了以下两种方式来获取,

    1. CURRENT_DATE
    2. CURRENT_TIME
    3. CURRENT_TIMESTAMP
    4. CURRENT_TIME(precision)
    5. CURRENT_TIMESTAMP(precision)
    6. LOCALTIME
    7. LOCALTIMESTAMP
    8. LOCALTIME(precision)
    9. LOCALTIMESTAMP(precision)

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

      CURRENT_TIMECURRENT_TIMESTAMP传递带有时区的值;

      LOCALTIMELOCALTIMESTAMP传递的值不带时区。

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

    1. SELECT CURRENT_TIME;
    2. 结果:14:39:53.662522-05
    3. SELECT CURRENT_DATE;
    4. 结果:2001-12-23
    5. SELECT CURRENT_TIMESTAMP;
    6. 结果:2001-12-23 14:39:53.662522-05
    7. SELECT CURRENT_TIMESTAMP(2);
    8. 结果:2001-12-23 14:39:53.66-05
    9. SELECT LOCALTIMESTAMP;
    10. 结果: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种格式的例子,

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

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

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

            我们来了解一下第②种。

    数据类型格式化函数

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

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

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

    1. SELECT to_char(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')
    2. --输出
    3. 2023-09-09 11:29:02

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

    1. SELECT to_timestamp('2023-09-09 11:29:02', 'YYYY-MM-DD HH24:MI:SS')
    2. --
    3. 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

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

    1. show time zone
    2. --Asia/Shanghai

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

    select * from pg_timezone_names;

            设置时区,

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

    set time zone "Asia/Shanghai"

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

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

    log_timezone = 'PRC' timezone = 'PRC'

            将配置文件的这两个变量的值设置成自己想要的时区(PRC指:People's Republic of China),然后重新加载即可:pg_ctl reload。

  • 相关阅读:
    微信小程序3(WXSS模板样式和全局局部配置)
    【Python】Pandas(学习笔记)
    ZYNQ之定时器
    使用[阿里问题定位神器]Arthas入门
    React组件通信-父子组件间的通信
    C++之OpenCV入门到提高003:矩阵的掩膜(Mask)处理
    Java---SSM---SpringMVC(2)
    你不知道的npm
    git使用
    2022/8/19 树莓派烧录与连接
  • 原文地址:https://blog.csdn.net/weixin_43524214/article/details/132774857