PostgreSQL 常见时间日期处理




  • 前言

    在实际业务开发过程中,通常会遇到对时间日期相关的处理,这里列出一些常见的时间日期处理方法。

    1. 时间戳带时区和不带时区
    创建表字段时,指定带时区时间日期数据类型
    postgres=# CREATE TABLE tab_test(id serial,app_name varchar,app_release_date timestamp with time zone default now());
    CREATE TABLE
    postgres=# INSERT INTO tab_test VALUES(1,'app');
    INSERT 0 1
    postgres=# select * from tab_test;
    id | app_name | app_release_date
    ----+----------+-------------------------------
    1 | app | 2021-10-11 15:24:05.730805+08
    (1 row)
    修改表字段为不带时区的数据类型
    postgres=# alter table tab_test alter column app_release_date set data type timestamp without time zone;
    ALTER TABLE
    postgres=# alter table tab_test alter column app_release_date set default now();
    ALTER TABLE
    postgres=# insert into tab_test values(2,'app2');
    INSERT 0 1
    postgres=# select * from tab_test;
    id | app_name | app_release_date
    ----+----------+----------------------------
    1 | app | 2021-10-11 15:24:05.730805
    2 | app2 | 2021-10-11 15:29:03.749597
    (2 rows)

    2.时间日期函数转换为带时区和不带时区的操作
    返回当前时间戳的函数
    postgres=# select current_timestamp,clock_timestamp();
    current_timestamp | clock_timestamp
    -------------------------------+-------------------------------
    2021-10-11 15:39:17.720835+08 | 2021-10-11 15:39:17.720974+08
    (1 row)
    返回当前时间戳函数不带时区

    postgres=# select current_timestamp::timestamp without time zone,clock_timestamp()::timestamp without time zone;
    current_timestamp | clock_timestamp
    ----------------------------+----------------------------
    2021-10-11 15:40:25.859267 | 2021-10-11 15:40:25.859363
    (1 row)

    3.将时间戳转换为从1970-01-01开始时的整数
    将当前时间转换为自1970-01-01开始到当前的整数
    postgres=# select extract(epoch from now());
    date_part
    -------------------
    1633938422.406166
    (1 row)

    上面函数 now() 是带有时区的,是否带时区和不带时区转换的整数不一致呢?

    postgres=# select extract(epoch from now()),extract(epoch from now()::timestamp without time zone);
    date_part | date_part
    -------------------+-------------------
    1633938525.014723 | 1633967325.014723
    (1 row)
    通过上面的示例,可以看出,如果将时间戳转换为整型,带有时区和不带有时区的值是不一样的。

    4.输出时间不带精度
    postgres=# select current_timestamp(0),current_time(0),localtime(0),localtimestamp(0);
    current_timestamp | current_time | localtime | localtimestamp
    ------------------------+--------------+-----------+---------------------
    2021-10-11 16:00:56+08 | 16:00:56+08 | 16:00:56 | 2021-10-11 16:00:56
    (1 row)
    在 PostgreSQL 中,时间戳函数和时间函数默认保留6位精度,只需要将时间精度保留为0即可去除精度。

    5.将输出不带精度的时间转换为整数

    postgres=# select extract(epoch from current_timestamp(0)) ;
    date_part
    ------------
    1634200047
    (1 row)

    6.将epoch整数转换为时间
    postgres=# select to_timestamp(1634200047);
    to_timestamp
    ------------------------
    2021-10-14 16:27:27+08
    (1 row)

    7.将时间按照时间域进行分解

    将时间按照时间域进行分解,有两个函数可以实现,一个是extract函数,一个是date_part函数
    postgres=# select date_part('month',now()),extract('month' from now());
    date_part | date_part
    -----------+-----------
    10 | 10
    (1 row)

    8.生成随机根据传入的参数生成随机日期

    create or replace function random_date(date,date,integer,integer)
    returns date
    as
    $function$
    --声明随机数
    declare
    random_range integer;
    random_days integer;
    random_start_date date;
    begin
    --指定随机范围
    random_range = $4 - $3;
    --随机天数设置
    random_days = trunc(random()*random_range);
    --随机日期需要从传入参数的起始值开始
    random_start_date = $1 + random_days;
    if random_start_date > $2 then
    raise notice '随机日期不能大于结束日期';
    return $2;
    else
    return random_start_date;
    end if;
    end;

    $function$
    language plpgsql;

    postgres=# select random_date('2001-01-02','2008-02-03',100,3000);
    random_date
    -------------
    2006-08-03
    (1 row)

    postgres=# select random_date('2001-01-02','2008-02-03',100,3000);
    random_date
    -------------
    2004-07-24
    (1 row)

    postgres=# select random_date('2001-01-02','2008-02-03',100,30000);
    NOTICE: 随机日期不能大于结束日期
    random_date
    -------------
    2008-02-03
    (1 row)

    9.测试随机日期插入数据

    postgres=# create table tab_random_date(id serial,p_date date);
    CREATE TABLE
    postgres=# insert into tab_random_date(p_date) select random_date('2001-01-02','2008-02-03',100,id) from generate_series(1,1000) as id;
    INSERT 0 1000

    postgres=# select count(*),p_date from tab_random_date group by p_date;
    count | p_date
    -------+------------
    3 | 2002-01-23
    1 | 2001-10-14
    6 | 2001-01-29
    1 | 2002-04-19
    1 | 2002-05-17
    4 | 2000-12-16

     

    Speak Your Mind

    *