postgresql 语句的汇总

柔情只为你懂 2022-08-19 12:11 232阅读 0赞

select now()::timestamp

select now()::date

select date\_part('day', now()::timestamp - '2016-1-11'::timestamp)

下表列出了基本算术操作符的行为：

<table style="margin:8px 0px; padding:0px; border:1px solid rgb(214,214,214); font-size:14px; line-height:21px; width:560px; border-collapse:collapse; vertical-align:top; color:rgb(0,0,0); font-family:Helvetica,Arial,sans-serif; background-color:rgb(247,247,247)"> 
 <tbody style="margin:0px; padding:0px"> 
  <tr style="margin:0px; padding:0px"> 
   <th style="border:1px solid rgb(214,214,214); font-size:10.5pt; margin:0px; padding:5px; border-collapse:collapse; background-color:rgb(238,238,238)"> 运算符</th> 
   <th style="border:1px solid rgb(214,214,214); font-size:10.5pt; margin:0px; padding:5px; border-collapse:collapse; width:250px; background-color:rgb(238,238,238)"> 实例</th> 
   <th style="border:1px solid rgb(214,214,214); font-size:10.5pt; margin:0px; padding:5px; border-collapse:collapse; background-color:rgb(238,238,238)"> 结果</th> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> +</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> date '2001-09-28' + integer '7'</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> date '2001-10-05'</td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> +</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> date '2001-09-28' + interval '1 hour'</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> timestamp '2001-09-28 01:00:00'</td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> +</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> date '2001-09-28' + time '03:00'</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> timestamp '2001-09-28 03:00:00'</td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> +</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> interval '1 day' + interval '1 hour'</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> interval '1 day 01:00:00'</td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> +</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> timestamp '2001-09-28 01:00' + interval '23 hours'</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> timestamp '2001-09-29 00:00:00'</td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> +</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> time '01:00' + interval '3 hours'</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> time '04:00:00'</td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> -</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> - interval '23 hours'</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> interval '-23:00:00'</td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> -</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> date '2001-10-01' - date '2001-09-28'</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> integer '3' (days)</td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> -</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> date '2001-10-01' - integer '7'</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> date '2001-09-24'</td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> -</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> date '2001-09-28' - interval '1 hour'</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> timestamp '2001-09-27 23:00:00'</td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> -</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> time '05:00' - time '03:00'</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> interval '02:00:00'</td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> -</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> time '05:00' - interval '2 hours'</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> time '03:00:00'</td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> -</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> timestamp '2001-09-28 23:00' - interval '23 hours'</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> timestamp '2001-09-28 00:00:00'</td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> -</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> interval '1 day' - interval '1 hour'</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> interval '1 day -01:00:00'</td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> -</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> interval '1 day 15:00:00'</td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> *</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> 900 * interval '1 second'</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> interval '00:15:00'</td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> *</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> 21 * interval '1 day'</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> interval '21 days'</td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> *</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> double precision '3.5' * interval '1 hour'</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> interval '03:30:00'</td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> /</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> interval '1 hour' / double precision '1.5'</td> 
   <td style="font-size:9.5pt; border-collapse:collapse; border:1px solid rgb(214,214,214); padding:5px; margin:0px"> interval '00:40:00'</td> 
  </tr> 
 </tbody> 
</table>

以下是所有重要的日期和时间相关的可用功能列表中。

## AGE(timestamp, timestamp),   AGE(timestamp) ##

例如时代功能 AGE(timestamp, timestamp)是：

testdb=# SELECT AGE(timestamp '2001-04-10', timestamp '1957-06-13'); yiibai.com

以上PostgreSQL的表会产生以下结果：

age
    -------------------------
     43 years 9 mons 27 days

功能AGE（timestamp ）的例子是：

testdb=# select age(timestamp '1957-06-13'); yiibai.com

Above PostgreSQL statement will produce following result:

age
    --------------------------
     55 years 10 mons 22 days
     yiibai.com

## CURRENT DATE/TIME() ##

PostgreSQL提供了返回值的函数的当前日期和时间相关。以下是一些函数：

例子使用的功能表所示：

testdb=# SELECT CURRENT_TIME;
           timetz
    --------------------
     08:01:34.656+05:30
    (1 row)
    
    
    testdb=# SELECT CURRENT_DATE;
        date
    ------------
     2013-05-05
    (1 row)
    
    
    testdb=# SELECT CURRENT_TIMESTAMP;
                  now
    -------------------------------
     2013-05-05 08:01:45.375+05:30
    (1 row)
    
    
    testdb=# SELECT CURRENT_TIMESTAMP(2);
             timestamptz
    ------------------------------
     2013-05-05 08:01:50.89+05:30
    (1 row)
    
    
    testdb=# SELECT LOCALTIMESTAMP;
           timestamp
    ------------------------
     2013-05-05 08:01:55.75
    (1 row)

PostgreSQL还提供了当前语句的开始时间，以及当前的实际时间的瞬间，该函数被调用的函数返回。这些函数包括：

## DATE\_PART(text, timestamp),   DATE\_PART(text, interval),   DATE\_TRUNC(text, timestamp) ##

Following are examples for DATE\_PART('field', source) functions:

testdb=# SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
     date_part
    -----------
            16
    (1 row)
    
    
    testdb=# SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
     date_part
    -----------
             4
    (1 row)

Following are examples for DATE\_TRUNC('field', source) functions:

testdb=# SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
         date_trunc
    ---------------------
     2001-02-16 20:00:00
    (1 row)
    
    
    testdb=# SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
         date_trunc
    ---------------------
     2001-01-01 00:00:00
    (1 row)

## EXTRACT(field from timestamp),   EXTRACT(field from interval) ##

The EXTRACT(field FROM source) function retrieves subfields such as year or hour from date/time values. source must be a value expression of type timestamp, time, or interval. field is an identifier or string that selects what field to extract from the source value. The EXTRACT function returns values of type double precision.

The following are valid field names (similar to DATE\_PART function field names):century, day, decade, dow, doy, epoch, hour, isodow, isoyear, microseconds, millennium, milliseconds, minute, month, quarter, second, timezone, timezone\_hour, timezone\_minute, week, year.

以下是EXTRACT('field', source) 函数的例子:

testdb=# SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
     date_part
    -----------
            20
    (1 row)
    
    
    testdb=# SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
     date_part
    -----------
            16
    (1 row) yiibai.com

## ISFINITE(date),   ISFINITE(timestamp),   ISFINITE(interval) ##

以下是为ISFINITE()函数的例子：

testdb=# SELECT isfinite(date '2001-02-16');
     isfinite
    ----------
     t
    (1 row)
    
    
    testdb=# SELECT isfinite(timestamp '2001-02-16 21:28:30');
     isfinite
    ----------
     t
    (1 row)
    
    
    testdb=# SELECT isfinite(interval '4 hours');
     isfinite
    ----------
     t
    (1 row)

## JUSTIFY\_DAYS(interval),   JUSTIFY\_HOURS(interval),   JUSTIFY\_INTERVAL(interval) ##

以下是为ISFINITE（）函数的例子：

testdb=# SELECT justify_days(interval '35 days');
     justify_days
    --------------
     1 mon 5 days
    (1 row)
    
    
    testdb=# SELECT justify_hours(interval '27 hours');
     justify_hours
    ----------------
     1 day 03:00:00
    (1 row)
    
    
    testdb=# SELECT justify_interval(interval '1 mon -1 hour');
     justify_interval
    ------------------
     29 days 23:00:00
    (1 row)