PostgreSQL学习手册(格式化时间函数详解)

墨蓝 2022-04-16 20:47 356阅读 0赞

### 1、简介 ###

*  最近在学习PostgreSQL 遇到了一些麻烦，就是时间的转换问题，然后找了好久终于被我找到了一个，码上。

### 2、数据类型格式化函数： ###

*  PostgreSQL格式化函数提供一套有效的工具用于把各种数据类型(日期/时间、integer、floating point和numeric)转换成格式化的字符串以及反过来从格式化的字符串转换成指定的数据类型。下面列出了这些函数，它们都遵循一个公共的调用习惯：第一个参数是待格式化的值，而第二个是定义输出或输出格式的模板。

<table> 
 <tbody> 
  <tr> 
   <td><strong>函数</strong></td> 
   <td><strong>返回类型</strong></td> 
   <td><strong>描述</strong></td> 
   <td><strong>例子</strong></td> 
  </tr> 
  <tr> 
   <td>to_char(timestamp, text)</td> 
   <td>text</td> 
   <td>把时间戳转换成字串</td> 
   <td>to_char(current_timestamp, 'HH12:MI:SS')</td> 
  </tr> 
  <tr> 
   <td>to_char(interval, text)</td> 
   <td>text</td> 
   <td>把时间间隔转为字串</td> 
   <td>to_char(interval '15h 2m 12s', 'HH24:MI:SS')</td> 
  </tr> 
  <tr> 
   <td>to_char(int, text)</td> 
   <td>text</td> 
   <td>把整数转换成字串</td> 
   <td>to_char(125, '999')</td> 
  </tr> 
  <tr> 
   <td>to_char(double precision, text)</td> 
   <td>text</td> 
   <td>把实数/双精度数转换成字串</td> 
   <td>to_char(125.8::real, '999D9')</td> 
  </tr> 
  <tr> 
   <td>to_char(numeric, text)</td> 
   <td>text</td> 
   <td>把numeric转换成字串</td> 
   <td>to_char(-125.8, '999D99S')</td> 
  </tr> 
  <tr> 
   <td>to_date(text, text)</td> 
   <td>date</td> 
   <td>把字串转换成日期</td> 
   <td>to_date('05 Dec 2000', 'DD Mon YYYY')</td> 
  </tr> 
  <tr> 
   <td>to_timestamp(text, text)</td> 
   <td>timestamp</td> 
   <td>把字串转换成时间戳</td> 
   <td>to_timestamp('05 Dec 2000', 'DD Mon YYYY')</td> 
  </tr> 
  <tr> 
   <td>to_timestamp(double)</td> 
   <td>timestamp</td> 
   <td>把UNIX纪元转换成时间戳</td> 
   <td>to_timestamp(200120400)</td> 
  </tr> 
  <tr> 
   <td>to_number(text, text)</td> 
   <td>numeric</td> 
   <td>把字串转换成numeric</td> 
   <td>to_number('12,454.8-', '99G999D9S')</td> 
  </tr> 
 </tbody> 
</table>

1. 用于日期/时间格式化的模式：

<table> 
 <tbody> 
  <tr> 
   <td><strong>模式</strong></td> 
   <td><strong>描述</strong></td> 
  </tr> 
  <tr> 
   <td>HH</td> 
   <td>一天的小时数(01-12)</td> 
  </tr> 
  <tr> 
   <td>HH12</td> 
   <td>一天的小时数(01-12)</td> 
  </tr> 
  <tr> 
   <td>HH24</td> 
   <td>一天的小时数(00-23)</td> 
  </tr> 
  <tr> 
   <td>MI</td> 
   <td>分钟(00-59)</td> 
  </tr> 
  <tr> 
   <td>SS</td> 
   <td>秒(00-59)</td> 
  </tr> 
  <tr> 
   <td>MS</td> 
   <td>毫秒(000-999)</td> 
  </tr> 
  <tr> 
   <td>US</td> 
   <td>微秒(000000-999999)</td> 
  </tr> 
  <tr> 
   <td>AM</td> 
   <td>正午标识(大写)</td> 
  </tr> 
  <tr> 
   <td>Y,YYY</td> 
   <td>带逗号的年(4和更多位)</td> 
  </tr> 
  <tr> 
   <td>YYYY</td> 
   <td>年(4和更多位)</td> 
  </tr> 
  <tr> 
   <td>YYY</td> 
   <td>年的后三位</td> 
  </tr> 
  <tr> 
   <td>YY</td> 
   <td>年的后两位</td> 
  </tr> 
  <tr> 
   <td>Y</td> 
   <td>年的最后一位</td> 
  </tr> 
  <tr> 
   <td>MONTH</td> 
   <td>全长大写月份名(空白填充为9字符)</td> 
  </tr> 
  <tr> 
   <td>Month</td> 
   <td>全长混合大小写月份名(空白填充为9字符)</td> 
  </tr> 
  <tr> 
   <td>month</td> 
   <td>全长小写月份名(空白填充为9字符)</td> 
  </tr> 
  <tr> 
   <td>MON</td> 
   <td>大写缩写月份名(3字符)</td> 
  </tr> 
  <tr> 
   <td>Mon</td> 
   <td>缩写混合大小写月份名(3字符)</td> 
  </tr> 
  <tr> 
   <td>mon</td> 
   <td>小写缩写月份名(3字符)</td> 
  </tr> 
  <tr> 
   <td>MM</td> 
   <td>月份号(01-12)</td> 
  </tr> 
  <tr> 
   <td>DAY</td> 
   <td>全长大写日期名(空白填充为9字符)</td> 
  </tr> 
  <tr> 
   <td>Day</td> 
   <td>全长混合大小写日期名(空白填充为9字符)</td> 
  </tr> 
  <tr> 
   <td>day</td> 
   <td>全长小写日期名(空白填充为9字符)</td> 
  </tr> 
  <tr> 
   <td>DY</td> 
   <td>缩写大写日期名(3字符)</td> 
  </tr> 
  <tr> 
   <td>Dy</td> 
   <td>缩写混合大小写日期名(3字符)</td> 
  </tr> 
  <tr> 
   <td>dy</td> 
   <td>缩写小写日期名(3字符)</td> 
  </tr> 
  <tr> 
   <td>DDD</td> 
   <td>一年里的日子(001-366)</td> 
  </tr> 
  <tr> 
   <td>DD</td> 
   <td>一个月里的日子(01-31)</td> 
  </tr> 
  <tr> 
   <td>D</td> 
   <td>一周里的日子(1-7；周日是1)</td> 
  </tr> 
  <tr> 
   <td>W</td> 
   <td>一个月里的周数(1-5)(第一周从该月第一天开始)</td> 
  </tr> 
  <tr> 
   <td>WW</td> 
   <td>一年里的周数(1-53)(第一周从该年的第一天开始)</td> 
  </tr> 
 </tbody> 
</table>

2. 用于数值格式化的模板模式：

<table> 
 <tbody> 
  <tr> 
   <td><strong>模式</strong></td> 
   <td><strong>描述</strong></td> 
  </tr> 
  <tr> 
   <td>9</td> 
   <td>带有指定数值位数的值</td> 
  </tr> 
  <tr> 
   <td>0</td> 
   <td>带前导零的值</td> 
  </tr> 
  <tr> 
   <td>.(句点)</td> 
   <td>小数点</td> 
  </tr> 
  <tr> 
   <td>,(逗号)</td> 
   <td>分组(千)分隔符</td> 
  </tr> 
  <tr> 
   <td>PR</td> 
   <td>尖括号内负值</td> 
  </tr> 
  <tr> 
   <td>S</td> 
   <td>带符号的数值</td> 
  </tr> 
  <tr> 
   <td>L</td> 
   <td>货币符号</td> 
  </tr> 
  <tr> 
   <td>D</td> 
   <td>小数点</td> 
  </tr> 
  <tr> 
   <td>G</td> 
   <td>分组分隔符</td> 
  </tr> 
  <tr> 
   <td>MI</td> 
   <td>在指明的位置的负号(如果数字 &lt; 0)</td> 
  </tr> 
  <tr> 
   <td>PL</td> 
   <td>在指明的位置的正号(如果数字 &gt; 0)</td> 
  </tr> 
  <tr> 
   <td>SG</td> 
   <td>在指明的位置的正/负号</td> 
  </tr> 
 </tbody> 
</table>

**八、时间/日期函数和操作符：**  
  
    1. 下面是PostgreSQL中支持的时间/日期操作符的列表：

<table> 
 <tbody> 
  <tr> 
   <td><strong>操作符</strong></td> 
   <td><strong>例子</strong></td> 
   <td><strong>结果</strong></td> 
  </tr> 
  <tr> 
   <td>+</td> 
   <td>date '2001-09-28' + integer '7'</td> 
   <td>date '2001-10-05'</td> 
  </tr> 
  <tr> 
   <td>+</td> 
   <td>date '2001-09-28' + interval '1 hour'</td> 
   <td>timestamp '2001-09-28 01:00'</td> 
  </tr> 
  <tr> 
   <td>+</td> 
   <td>date '2001-09-28' + time '03:00'</td> 
   <td>timestamp '2001-09-28 03:00'</td> 
  </tr> 
  <tr> 
   <td>+</td> 
   <td>interval '1 day' + interval '1 hour'</td> 
   <td>interval '1 day 01:00'</td> 
  </tr> 
  <tr> 
   <td>+</td> 
   <td>timestamp '2001-09-28 01:00' + interval '23 hours'</td> 
   <td>timestamp '2001-09-29 00:00'</td> 
  </tr> 
  <tr> 
   <td>+</td> 
   <td>time '01:00' + interval '3 hours'</td> 
   <td>time '04:00'</td> 
  </tr> 
  <tr> 
   <td>-</td> 
   <td>- interval '23 hours'</td> 
   <td>interval '-23:00'</td> 
  </tr> 
  <tr> 
   <td>-</td> 
   <td>date '2001-10-01' - date '2001-09-28'</td> 
   <td>integer '3'</td> 
  </tr> 
  <tr> 
   <td>-</td> 
   <td>date '2001-10-01' - integer '7'</td> 
   <td>date '2001-09-24'</td> 
  </tr> 
  <tr> 
   <td>-</td> 
   <td>date '2001-09-28' - interval '1 hour'</td> 
   <td>timestamp '2001-09-27 23:00'</td> 
  </tr> 
  <tr> 
   <td>-</td> 
   <td>time '05:00' - time '03:00'</td> 
   <td>interval '02:00'</td> 
  </tr> 
  <tr> 
   <td>-</td> 
   <td>time '05:00' - interval '2 hours'</td> 
   <td>time '03:00'</td> 
  </tr> 
  <tr> 
   <td>-</td> 
   <td>timestamp '2001-09-28 23:00' - interval '23 hours'</td> 
   <td>timestamp '2001-09-28 00:00'</td> 
  </tr> 
  <tr> 
   <td>-</td> 
   <td>interval '1 day' - interval '1 hour'</td> 
   <td>interval '23:00'</td> 
  </tr> 
  <tr> 
   <td>-</td> 
   <td>timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'</td> 
   <td>interval '1 day 15:00'</td> 
  </tr> 
  <tr> 
   <td>*</td> 
   <td>interval '1 hour' * double precision '3.5'</td> 
   <td>interval '03:30'</td> 
  </tr> 
  <tr> 
   <td>/</td> 
   <td>interval '1 hour' / double precision '1.5'</td> 
   <td>interval '00:40'</td> 
  </tr> 
 </tbody> 
</table>

2. 日期/时间函数：

<table> 
 <tbody> 
  <tr> 
   <td><strong>函数</strong></td> 
   <td><strong>返回类型</strong></td> 
   <td><strong>描述</strong></td> 
   <td><strong>例子</strong></td> 
   <td><strong>结果</strong></td> 
  </tr> 
  <tr> 
   <td>age(timestamp, timestamp)</td> 
   <td>interval</td> 
   <td>减去参数，生成一个使用年、月的"符号化"的结果</td> 
   <td>age('2001-04-10', timestamp '1957-06-13')</td> 
   <td>43 years 9 mons 27 days</td> 
  </tr> 
  <tr> 
   <td>age(timestamp)</td> 
   <td>interval</td> 
   <td>从current_date减去得到的数值</td> 
   <td>age(timestamp '1957-06-13')</td> 
   <td>43 years 8 mons 3 days</td> 
  </tr> 
  <tr> 
   <td>current_date</td> 
   <td>date</td> 
   <td>今天的日期</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>current_time</td> 
   <td>time</td> 
   <td>现在的时间</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>current_timestamp</td> 
   <td>timestamp</td> 
   <td>日期和时间</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>date_part(text, timestamp)</td> 
   <td>double</td> 
   <td>获取子域(等效于extract)</td> 
   <td>date_part('hour', timestamp '2001-02-16 20:38:40')</td> 
   <td>20</td> 
  </tr> 
  <tr> 
   <td>date_part(text, interval)</td> 
   <td>double</td> 
   <td>获取子域(等效于extract)</td> 
   <td>date_part('month', interval '2 years 3 months')</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>date_trunc(text, timestamp)</td> 
   <td>timestamp</td> 
   <td>截断成指定的精度</td> 
   <td>date_trunc('hour', timestamp '2001-02-16 20:38:40')</td> 
   <td>2001-02-16 20:00:00+00</td> 
  </tr> 
  <tr> 
   <td>extract(field from timestamp)</td> 
   <td>double</td> 
   <td>获取子域</td> 
   <td>extract(hour from timestamp '2001-02-16 20:38:40')</td> 
   <td>20</td> 
  </tr> 
  <tr> 
   <td>extract(field from interval)</td> 
   <td>double</td> 
   <td>获取子域</td> 
   <td>extract(month from interval '2 years 3 months')</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>localtime</td> 
   <td>time</td> 
   <td>今日的时间</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>localtimestamp</td> 
   <td>timestamp</td> 
   <td>日期和时间</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>now()</td> 
   <td>timestamp</td> 
   <td>当前的日期和时间(等效于 current_timestamp)</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>timeofday()</td> 
   <td>text</td> 
   <td>当前日期和时间</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

3. EXTRACT，date\_part函数支持的field：

<table> 
 <tbody> 
  <tr> 
   <td><strong>域</strong></td> 
   <td><strong>描述</strong></td> 
   <td><strong>例子</strong></td> 
   <td><strong>结果</strong></td> 
  </tr> 
  <tr> 
   <td>CENTURY</td> 
   <td>世纪</td> 
   <td>EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');</td> 
   <td>20</td> 
  </tr> 
  <tr> 
   <td>DAY</td> 
   <td>(月分)里的日期域(1-31)</td> 
   <td>EXTRACT(DAY from TIMESTAMP '2001-02-16 20:38:40');</td> 
   <td>16</td> 
  </tr> 
  <tr> 
   <td>DECADE</td> 
   <td>年份域除以10</td> 
   <td>EXTRACT(DECADE from TIMESTAMP '2001-02-16 20:38:40');</td> 
   <td>200</td> 
  </tr> 
  <tr> 
   <td>DOW</td> 
   <td>每周的星期号(0-6；星期天是0) (仅用于timestamp)</td> 
   <td>EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');</td> 
   <td>5</td> 
  </tr> 
  <tr> 
   <td>DOY</td> 
   <td>一年的第几天(1 -365/366) (仅用于 timestamp)</td> 
   <td>EXTRACT(DOY from TIMESTAMP '2001-02-16 20:38:40');</td> 
   <td>47</td> 
  </tr> 
  <tr> 
   <td>HOUR</td> 
   <td>小时域(0-23)</td> 
   <td>EXTRACT(HOUR from TIMESTAMP '2001-02-16 20:38:40');</td> 
   <td>20</td> 
  </tr> 
  <tr> 
   <td>MICROSECONDS</td> 
   <td>秒域，包括小数部分，乘以 1,000,000。</td> 
   <td>EXTRACT(MICROSECONDS from TIME '17:12:28.5');</td> 
   <td>28500000</td> 
  </tr> 
  <tr> 
   <td>MILLENNIUM</td> 
   <td>千年</td> 
   <td>EXTRACT(MILLENNIUM from TIMESTAMP '2001-02-16 20:38:40');</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>MILLISECONDS</td> 
   <td>秒域，包括小数部分，乘以 1000。</td> 
   <td>EXTRACT(MILLISECONDS from TIME '17:12:28.5');</td> 
   <td>28500</td> 
  </tr> 
  <tr> 
   <td>MINUTE</td> 
   <td>分钟域(0-59)</td> 
   <td>EXTRACT(MINUTE from TIMESTAMP '2001-02-16 20:38:40');</td> 
   <td>38</td> 
  </tr> 
  <tr> 
   <td>MONTH</td> 
   <td>对于timestamp数值，它是一年里的月份数(1-12)；对于interval数值，它是月的数目，然后对12取模(0-11)</td> 
   <td>EXTRACT(MONTH from TIMESTAMP '2001-02-16 20:38:40');</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>QUARTER</td> 
   <td>该天所在的该年的季度(1-4)(仅用于 timestamp)</td> 
   <td>EXTRACT(QUARTER from TIMESTAMP '2001-02-16 20:38:40');</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>SECOND</td> 
   <td>秒域，包括小数部分(0-59[1])</td> 
   <td>EXTRACT(SECOND from TIMESTAMP '2001-02-16 20:38:40');</td> 
   <td>40</td> 
  </tr> 
  <tr> 
   <td>WEEK</td> 
   <td>该天在所在的年份里是第几周。</td> 
   <td>EXTRACT(WEEK from TIMESTAMP '2001-02-16 20:38:40');</td> 
   <td>7</td> 
  </tr> 
  <tr> 
   <td>YEAR</td> 
   <td>年份域</td> 
   <td>EXTRACT(YEAR from TIMESTAMP '2001-02-16 20:38:40');</td> 
   <td>2001</td> 
  </tr> 
 </tbody> 
</table>

4. 当前日期/时间：  
    我们可以使用下面的函数获取当前的日期和/或时间∶  
    CURRENT\_DATE  
    CURRENT\_TIME  
    CURRENT\_TIMESTAMP  
    CURRENT\_TIME (precision)  
    CURRENT\_TIMESTAMP (precision)  
    LOCALTIME  
    LOCALTIMESTAMP  
    LOCALTIME (precision)  
    LOCALTIMESTAMP (precision)

详细请参考原网址：

[http://www.cnblogs.com/stephen-liu74/archive/2012/05/04/2294643.html][http_www.cnblogs.com_stephen-liu74_archive_2012_05_04_2294643.html]

[http_www.cnblogs.com_stephen-liu74_archive_2012_05_04_2294643.html]: http://www.cnblogs.com/stephen-liu74/archive/2012/05/04/2294643.html