PostgreSQL common time and date processing

Time:2021-10-28

preface

In the actual business development process, we usually encounter time and date related processing. Here are some common time and date processing methods.

1. Timestamp with and without time zone

When creating a table field, specify the time date data type with time zone

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)

Modify the table field to a data type without time zone


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. The time date function is converted to the operation with and without time zone

Function that returns the current timestamp


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)

Returns the current timestamp function without time zone


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. Convert the timestamp to an integer starting from January 1, 1970

Convert the current time to an integer from January 1, 1970 to the current time


postgres=# select extract(epoch from now());
     date_part     
    -------------------
    1633938422.406166
    (1 row)

The above function now () has a time zone. Are integers with and without time zone conversion inconsistent?


postgres=# select extract(epoch from now()),extract(epoch from now()::timestamp without time zone);
     date_part     |     date_part     
    -------------------+-------------------
    1633938525.014723 | 1633967325.014723
    (1 row)

From the above example, we can see that if the timestamp is converted to integer, the values with and without time zone are different.

4. Output time without precision


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)

In PostgreSQL, the timestamp function and time function retain 6-bit precision by default. You only need to keep the time precision to 0 to remove the precision.

5. Convert the output time without precision to an integer


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

7. Decompose time according to time domain

Decompose time according to time domain. There are two functions to implement, one is extract function and the other is date_ Part function


postgres=# select date_part('month',now()),extract('month' from now());
   date_part | date_part 
  -----------+-----------
          10 |        10
  (1 row)

8. Generate random date based on the passed in parameters

create or replace function random_date(date,date,integer,integer)
  returns date
  as
  $function$
  --Declare random number
  declare
    random_range integer;
    random_days   integer;
    random_start_date date;
  begin
    --Specify random range
    random_range = $4 - $3;
    --Random days setting
    random_days = trunc(random()*random_range);
    --The random date needs to start from the starting value of the passed in parameter
    random_start_date = $1 + random_days;
    if random_start_date > $2 then
      Raise notice 'random date cannot be later than end date';
      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);
  Note: the random date cannot be later than the end date
   random_date 
  -------------
   2008-02-03
  (1 row)

9. Test random date insertion data


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