关于postgresql中的时间戳格式化浅谈

短命女 2024-03-23 20:49 24阅读 0赞

![c129adf1db97a59450ed60bcc07c92a3.jpeg][]

本文主要介绍了postgresql中的时间戳格式化问题，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教

### postgresql时间戳格式化 ###

平时总记不住pg里面怎么将时间戳格式化，记录常用的格式化时间

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> <p>5</p> <p>6</p> <p>7</p> <p>8</p> <p>9</p> <p>10</p> <p>11</p> <p>12</p> </td> 
   <td> <p><code>-- 年</code></p> <p><code>select</code> <code>to_char(to_timestamp(1608018517000 / 1000),</code><code>'yyyy'</code><code>);&nbsp; </code></p> <p><code>-- 年-月</code></p> <p><code>select</code> <code>to_char(to_timestamp(1608018517000 / 1000),</code><code>'yyyy-MM'</code><code>);</code></p> <p><code>-- 年-月-日</code></p> <p><code>select</code> <code>to_char(to_timestamp(1608018517000 / 1000),</code><code>'yyyy-MM-dd'</code><code>); </code></p> <p><code>-- 年-月-日 时</code></p> <p><code>select</code> <code>to_char(to_timestamp(1608018517000 / 1000),</code><code>'yyyy-MM-dd HH24'</code><code>); 对应JAVA 的“yyyy-MM-dd HH”</code></p> <p><code>-- 年-月-日 时:分&nbsp; </code></p> <p><code>select</code> <code>to_char(to_timestamp(1608018517000 / 1000),</code><code>'yyyy-MM-dd HH24:MI'</code><code>);对应JAVA 的“yyyy-MM-dd HH:mm”</code></p> <p><code>-- 年-月-日 时:分:秒&nbsp; </code></p> <p><code>select</code> <code>to_char(to_timestamp(1608018517000 / 1000),</code><code>'yyyy-MM-dd HH24:MI:SS'</code><code>);对应JAVA 的“yyyy-MM-dd HH:mm:ss”</code></p> </td> 
  </tr> 
 </tbody> 
</table>

### postgresql中时间戳格式转化常识 ###

前提：当数据库中保存的是timestamp类型时，我们需要通过这个时间戳来做乐观数据锁，那么久需要Select出来，然后在更新的时候在Update的where条件中判断时间戳是否与查询时相同。

下面的SQL文查询结果是 "2018-08-20 10:09:10.815125"，并且返回类型可以当String处理。返回json等都方便使用。

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>SQL&gt; </code><code>select</code> <code>to_char(updateTime, </code><code>'yyyy-mm-dd hh24:mi:ss.us'</code><code>) </code><code>from</code> <code>tbl_A;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

更新时，参数传入“2018-08-20 10:09:10.815125”的字符串，那么需要在SQL中转化来匹配updateTime字段的timeStamp数据类型。

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>SQL&gt; </code><code>update</code> <code>tbl_A </code><code>set</code> <code>username=</code><code>'XXX'</code> <code>where</code> <code>userid=</code><code>'001'</code> <code>and</code> <code>updateTime = to_timestamp(</code><code>'2018-08-20 10:09:10.815125'</code><code>,</code><code>'yyyy-mm-dd hh24:mi:ss.us'</code><code>);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

#### 另附表一张 ####

<table> 
 <tbody> 
  <tr> 
   <td>函数</td> 
   <td>返回类型</td> 
   <td>描述</td> 
   <td>例子</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>

<table> 
 <tbody> 
  <tr> 
   <td>模式</td> 
   <td>描述</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>

### 总结 ###

以上为个人经验，希望能给大家一个参考！

转自：[微点阅读][Link 1] [https://www.weidianyuedu.com][https_www.weidianyuedu.com]

[c129adf1db97a59450ed60bcc07c92a3.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/a068ffe1f98343199356406bfe4af945.png
[Link 1]: https://m.weidianyuedu.com/
[https_www.weidianyuedu.com]: https://www.weidianyuedu.com