derby 數據庫中 TIMESTAMPDIFF函數怎么用
TIMESTAMPDIFF is a JDBC escaped function, and is only accessible using the JDBC escape function syntax. The syntax you need is:
lect {fn timestampdiff(SQL_TSI_WEEK, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)} from demo.field;
TIMESTAMPDIFF( interval, timestampExpression1, timestampExpression2 )
三個參數，第二和第三個參數是timestamp類型的時間函數，第一個參數是（timestampExpression2-timestampExpression1）的時間差的表示單位，如SQL_TSI_SECOND是以秒為單位，返回兩個查詢參數的時間差。
1）interval的類型還有：SQL_TSI_DAY
、SQL_TSI_FRAC_SECOND、SQL_TSI_HOUR、SQL_TSI_MINUTE、SQL_TSI_MONTH、
SQL_TSI_QUARTER、SQL_TSI_SECOND、SQL_TSI_WEEK、SQL_TSI_YEAR
mysqlupdatetimestampdiff報錯
mysqlupdatetimestampdiff報錯是由于版本問題引起的。解決方法是：
1、升級你的mysql版本，升級到最新版本即可解決問題。
2、如果不想升級，那么此時需要創建一個觸發器，通過觸發器在插入之前獲取當前時間，然后返回給createtim。
IBM DB2計算兩個時間戳記間的時間差
DB2本身的內置函數，缺點是是近似值，用法如下：
SELECT
timestampdiff (256, char(timestamp('2017-05-27 23:00:00') - timestamp('2017-05-25 10:40:00'))) AS "間隔年",
timestampdiff (128, char(timestamp('') - timestamp(''))) AS "間隔季度",
timestampdiff (64, char(timestamp('') - timestamp(''))) AS "間隔月",
timestampdiff (32, char(timestamp('') - timestamp(''))) AS "間隔周",
timestampdiff (16, char(timestamp('') - timestamp(''))) AS "間隔日",
timestampdiff (8, char(timestamp('') - timestamp(''))) AS "間隔時",
timestampdiff (4, char(timestamp('') - timestamp(''))) AS "間隔分",
timestampdiff (2, char(timestamp('') - timestamp(''))) AS "間隔秒"
FROM SYSIBM.SYSDUMMY1;
相見時難別亦難
如何處理mysql中的時間戳讀取問題
1. MySQL 獲得當前時間戳函數：current_timestamp, current_timestamp()
mysql> lect current_timestamp, current_timestamp();
+---------------------+---------------------+
| current_timestamp | current_timestamp() |
+---------------------+---------------------+
| 2008-08-09 23:22:24 | 2008-08-09 23:22:24 |
+---------------------+---------------------+
2. MySQL （Unix 時間戳、日期）轉換函數：
unix_timestamp(),
unix_timestamp(date),
from_unixtime(unix_timestamp),
from_unixtime(unix_timestamp,format)
下面是示例：
lect unix_timestamp(); -- 1218290027
lect unix_timestamp('2008-08-08'); -- 1218124800
lect unix_timestamp('2008-08-08 12:30:00'); -- 1218169800
lect from_unixtime(1218290027); -- '2008-08-09 21:53:47'
lect from_unixtime(1218124800); -- '2008-08-08 00:00:00'
lect from_unixtime(1218169800); -- '2008-08-08 12:30:00'
lect from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x'); -- '2008 8th August 12:30:00 2008'
3. MySQL 時間戳（timestamp）轉換、增、減函數：
timestamp(date) -- date to timestamp
timestamp(dt,time) -- dt + time
timestampadd(unit,interval,datetime_expr) --
timestampdiff(unit,datetime_expr1,datetime_expr2) --
請看示例部分：
lect timestamp('2008-08-08'); -- 2008-08-08 00:00:00
lect timestamp('2008-08-08 08:00:00', '01:01:01'); -- 2008-08-08 09:01:01
lect timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-18 09:01:01
lect timestampadd(day, 1, '2008-08-08 08:00:00'); -- 2008-08-09 08:00:00
lect date_add('2008-08-08 08:00:00', interval 1 day); -- 2008-08-09 08:00:00
MySQL timestampadd() 函數類似于 date_add()。
lect timestampdiff(year,'2002-05-01','2001-01-01'); -- -1
lect timestampdiff(day ,'2002-05-01','2001-01-01'); -- -485
lect timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); -- -12
lect datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00'); -- 7
MySQL timestampdiff() 函數就比 datediff() 功能強多了，datediff() 只能計算兩個日期（date）之間相差的天數。
