关于数据库: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)

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