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）之间相差的天数。
