SQLite时间函数&日期时间、字符串、时间戳互相转换

冷不防 2023-10-17 21:15 175阅读 0赞

‘now’为当前日期时间

##### \*\*转字符串、时间 #####

SELECT date('now');     -->结果：2018-05-05
    SELECT time('now');     -->结果：06:55:38
    SELECT datetime('now'); -->结果：2018-05-05 06:55:38
    SELECT strftime('%Y-%m-%d %H:%M:%S', 'now');    -->结果：2018-05-05 06:55:38

##### \*\*转时间戳 #####

select strftime('%s','now');    -->结果：1525502284
    select strftime('%s','2018-05-05');     -->结果：1525478400

##### 时间戳转时间、字符串 #####

select datetime(1525502284, 'unixepoch', 'localtime');  -->结果：2018-05-05 14:38:04
    --对参数不理解可参考文末“修饰符”

--------------------

#### 扩展：SQLite的五个时间函数： ####

1.  date(timestring, modifier, modifier, …)\*\*：以 YYYY-MM-DD 格式返回日期
2.  time(timestring, modifier, modifier, …)\*\*：以 HH:MM:SS 格式返回时间
3.  datetime(timestring, modifier, modifier, …)\*\*：以 YYYY-MM-DD HH:MM:SS 格式返回日期时间
4.  julianday(format, timestring, modifier, modifier, ..)：返回从格林尼治时间的公元前 4714 年 11 月 24 日正午算起的天数
5.  **strftime(format, timestring, modifier, modifier, ..)**：根据第一个参数指定的格式字符串返回格式化的日期

###### 讲道理其他四个函数都可以用 strftime() 函数来表示： ######

date(…) –> strftime(‘%Y-%m-%d’,…)  
time(…) –> strftime(‘%H:%M:%S’,…)  
datetime(…) –> strftime(‘%Y-%m-%d %H:%M:%S’,…)  
julianday(…) –> strftime(‘%J’,…)

#### 来看看各参数的取值格式（文末举例）： ####

##### 日期时间字符串（timestring）： #####

<table> 
 <thead> 
  <tr> 
   <th>序号</th> 
   <th align="center">日期时间字符串</th> 
   <th align="right">实例</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td align="center">YYYY-MM-DD</td> 
   <td align="right">2018-05-05</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td align="center">YYYY-MM-DD HH:MM</td> 
   <td align="right">2018-05-05 12:10</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td align="center">YYYY-MM-DD HH:MM:SS.SSS</td> 
   <td align="right">2018-05-05 15:39:20.100</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td align="center">MM-DD-YYYY HH:MM</td> 
   <td align="right">05-05-2018 12:10</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td align="center">HH:MM</td> 
   <td align="right">同理</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td align="center">YYYY-MM-DDTHH:MM</td> 
   <td align="right">同理</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td align="center">HH:MM:SS</td> 
   <td align="right">同理</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td align="center">YYYYMMDD HHMMSS</td> 
   <td align="right">同理</td> 
  </tr> 
  <tr> 
   <td>9</td> 
   <td align="center">now</td> 
   <td align="right">2018-05-05 15:39:20</td> 
  </tr> 
  <tr> 
   <td>10</td> 
   <td align="center">DDDDDDDDDD</td> 
   <td align="right">1525478400（时间戳）</td> 
  </tr> 
 </tbody> 
</table>

##### strftime() 函数，格式化串（format）： #####

<table> 
 <thead> 
  <tr> 
   <th>符号</th> 
   <th align="center">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>%d</td> 
   <td align="center">一月中的第几天 01-31</td> 
  </tr> 
  <tr> 
   <td>%f</td> 
   <td align="center">小数形式的秒，SS.SSSS</td> 
  </tr> 
  <tr> 
   <td>%H</td> 
   <td align="center">小时 00-24</td> 
  </tr> 
  <tr> 
   <td>%j</td> 
   <td align="center">一年中的第几天 01-366</td> 
  </tr> 
  <tr> 
   <td>%J</td> 
   <td align="center">Julian Day Numbers</td> 
  </tr> 
  <tr> 
   <td>%m</td> 
   <td align="center">月份 01-12</td> 
  </tr> 
  <tr> 
   <td>%M</td> 
   <td align="center">分钟 00-59</td> 
  </tr> 
  <tr> 
   <td>%s</td> 
   <td align="center">从 1970-01-01日开始计算的秒数</td> 
  </tr> 
  <tr> 
   <td>%S</td> 
   <td align="center">秒 00-59</td> 
  </tr> 
  <tr> 
   <td>%w</td> 
   <td align="center">星期，0-6，0是星期天</td> 
  </tr> 
  <tr> 
   <td>%W</td> 
   <td align="center">一年中的第几周 00-53</td> 
  </tr> 
  <tr> 
   <td>%Y</td> 
   <td align="center">年份 0000-9999</td> 
  </tr> 
  <tr> 
   <td>%%</td> 
   <td align="center">% 百分号</td> 
  </tr> 
 </tbody> 
</table>

##### 修饰符（modifier）： #####

<table> 
 <thead> 
  <tr> 
   <th>序号</th> 
   <th align="center">符号</th> 
   <th align="right">作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>１</td> 
   <td align="center">[+-]NNN years</td> 
   <td align="right">增加/减去指定数值的年</td> 
  </tr> 
  <tr> 
   <td>２</td> 
   <td align="center">[+-]NNN months</td> 
   <td align="right">增加/减去指定数值的月</td> 
  </tr> 
  <tr> 
   <td>３</td> 
   <td align="center">[+-]NNN days</td> 
   <td align="right">增加/减去指定数值的天</td> 
  </tr> 
  <tr> 
   <td>４</td> 
   <td align="center">[+-]NNN hours</td> 
   <td align="right">增加/减去指定数值的小时</td> 
  </tr> 
  <tr> 
   <td>５</td> 
   <td align="center">[+-]NNN minutes</td> 
   <td align="right">增加/减去指定数值的分钟</td> 
  </tr> 
  <tr> 
   <td>６</td> 
   <td align="center">[+-]NNN.NNNN seconds</td> 
   <td align="right">增加/减去指定数值的秒</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td align="center">start of year</td> 
   <td align="right">当前日期的开始年</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td align="center">start of month</td> 
   <td align="right">当前日期的开始月</td> 
  </tr> 
  <tr> 
   <td>9</td> 
   <td align="center">start of day</td> 
   <td align="right">当前日期的开始日</td> 
  </tr> 
  <tr> 
   <td>11</td> 
   <td align="center">weekday N</td> 
   <td align="right">表示返回下一个星期是N的日期和时间</td> 
  </tr> 
  <tr> 
   <td>12</td> 
   <td align="center">unixepoch</td> 
   <td align="right">用于将日期解释为UNIX时间(即：自1970-01-01以来的秒数，也就是时间戳)</td> 
  </tr> 
  <tr> 
   <td>13</td> 
   <td align="center">localtime</td> 
   <td align="right">表示返回本地时间</td> 
  </tr> 
  <tr> 
   <td>14</td> 
   <td align="center">utc</td> 
   <td align="right">表示返回UTC（世界统一时间）时间</td> 
  </tr> 
 </tbody> 
</table>

--------------------

### 重点：修饰符运用实例 ###

SELECT datetime('now'); -->结果：2018-05-05 08:10:26（和本机时间可能不一致，时区问题请看使用‘localtime’修饰符后的变化）
    SELECT datetime('now','1 years');-->结果：2019-05-05 08:10:26
    SELECT datetime('now','1 months');-->结果：2018-06-05 08:10:26
    SELECT datetime('now','1 days');-->结果：2018-05-06 08:10:26
    SELECT datetime('now','1 hours');-->结果：2018-05-05 09:10:26
    SELECT datetime('now','1 minutes');-->结果：2018-05-05 08:11:26
    SELECT datetime('now','1 seconds');-->结果：2018-05-05 08:10:27
    
    SELECT datetime('now','start of year');-->结果：2018-01-01 00:00:00
    SELECT datetime('now','start of month');-->结果：2018-05-01 00:00:00
    SELECT datetime('now','start of day');-->结果：2018-05-05 00:00:00
    
    SELECT datetime('now','weekday 0');-->结果：2018-05-06 08:10:26
    （解释：2018-05-05是周六，“weekday 0”表示返回下周的周日(系统默认以周日为一周的开始0)，明天06号是周日，所以，返回了2018-05-06）
    SELECT datetime('1525478400','unixepoch');-->结果：2018-05-05 00:00:00（unixepoch一般用于解释时间戳）
    SELECT datetime('now','localtime');-->结果：2018-05-05 16:10:26（你会发现这个时间比没使用‘localtime’参数的时间多了8个小时，因为中国是东八时区）
    SELECT datetime('now','utc');-->结果：2018-05-05 00:10:26（时区问题）

--------------------

水平有限，小文如有错漏，请各路大神多指点。