sqlite数据类型（时间 日期 ） timestamp 使用
timestamp使用
timestamp两种属性:
自动初始化: 此行为只在第一次写入数据时,怎么把时间设为当前时间. (DEFAULT CURRENT_TIMESTAMP)
自动更新: 此行为在修改资料时,会自动帮你把值修改为当前时间. (ON UPDATE CURRENT_TIMESTAMP)
| 自动初始化 | 第一次写入数据时把时间设为当前时间 | tiims1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP |
| 自动更新 | 每一次写入数据时把时间设为当前时间 | ON UPDATE CURRENT_TIMESTAMP |
| timestamp两种结合 | 系统默认 自动初始化以及自动更新 | TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP |
四种状态:由上面两种属性延伸出四种状态
| 自动初始化以及自动更新 | timestamp字段的数据默认就是此行为 |
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP |
|
| 只做初始化,更新时不自动更新 | ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP | ||
| 只做自动更新,不做初始化 | ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP | ||
| 什么都不做 | datetime | ||
下面这些语句是等效的：
CREATE TABLE t (ts TIMESTAMP); CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); CREATE TABLE t (ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
ts TIMESTAMP DEFAULT 0 //只是给一个常量（注：0000-00-00 00:00:00）
可以在TIMESTAMP列的定义中包括NULL属性以允许列包含NULL值。例如：
CREATE TABLE t ( ts1 TIMESTAMP NULL DEFAULT NULL, ts2 TIMESTAMP NULL DEFAULT 0, ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP );
未指定NULL属性，将列设置为NULL，用于将它设置为当前的时间戳。
注意允许NULL值的TIMESTAMP列不会采用当前的时间戳，除非要么其 默认值定义为CURRENT_TIMESTAMP，或者NOW()或CURRENT_TIMESTAMP被插入到该列内。换句话说，只有使用如下创建，定义为 NULL的TIMESTAMP列才会自动更新：
CREATE TABLE t (ts NULL DEFAULT CURRENT_TIMESTAMP)； CREATE TABLE t1 (ts NULL DEFAULT NULL); CREATE TABLE t2 (ts NULL DEFAULT '0000-00-00 00:00:00');
则必须显式插入一个对应当前日期和时间的值。例如：
INSERT INTO t1 VALUES (NOW()); INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);
sqlite3支持的数据类型：
NULL、INTEGER、REAL、TEXT、BLOB 但是，sqlite3也支持如下的数据类型 smallint 16位整数 integer 32位整数 decimal(p,s) p是精确值,s是小数位数 float 32位实数 double 64位实数 char(n) n长度字符串，不能超过254 varchar(n) 长度不固定最大字符串长度为n，n不超过4000 graphic(n) 和 char(n) 一样，但是单位是两个字符double-bytes，n不超过127(中文字) vargraphic(n) 可变长度且最大长度为n date 包含了年份、月份、日期 time 包含了小时、分钟、秒 timestamp 包含了年、月、日、时、分、秒、千分之一秒
sqlite3支持的函数
【1】日期函数
datetime() ： 产生日期和时间
date()： 产生日期
time()：产生时间
strftime()：对以上3个函数产生的日期和时间进行格式化
用法实例：
1、SELECT date('2011-9-9','+1 day','+1 year'); 结果是 2010-09-10
2、SELECT datetime('now'); 当前日期和时间
3、SELECT datetime('now', 'start of month'); 本月的第一天零点，也可以设置年和日的第一天
4、SELECT datetime('now','+1 hour','-12 minute'); 当前时间加48分钟
如果需要精确到毫秒，使用
select strftime('%Y-%m-%d %H:%M:%f','now')
结果：2017-04-12 17:48:35.890
strftime() 函数可以将 YYYY-MM-DD HH:MM:SS 格式的日期字符串转换为其它形式的字符串
%d：天数，01-31
%f ：小数形式的秒，SS.SSS
%H：小时
%j ：某一天是该年的第几天，001-366
%m：月份，00-12
%M：分钟，00-59
%s：从1970到现在的秒数
%S：秒，00-59
%w：星期，0-6，0是星期天
%W：某天是该年的第几周，01-53
%Y：年，YYYY
%% 百分号
样本表：job_history
employee_id start_date end_date job_id department_id ----------- ---------- ---------- ---------- --------- ---- 102 1993-01-13 1998-07-24 IT_PROG 60 101 1989-09-21 1993-10-27 AC_ACCOUNT 110 101 1993-10-28 1997-03-15 AC_MGR 110 201 1996-02-17 1999-12-19 MK_REP 20 114 1998-03-24 1999-12-31 ST_CLERK 50 122 1999-01-01 1999-12-31 ST_CLERK 50 200 1987-09-17 1993-06-17 AD_ASST 90 176 1998-03-24 1998-12-31 SA_REP 80 176 1999-01-01 1999-12-31 SA_MAN 80 200 1994-07-01 1998-12-31 AC_ACCOUNT 90
如果我们想从表job_history中查找start_date的年，月和日值，则可以使用以下SQL。
SELECT start_date,strftime('%Y',start_date) as "Year", strftime('%m',start_date) as "Month", strftime('%d',start_date) as "Day" FROM job_history;
结果
start_date年月日 ---------- ---------- ---------- ------ 1993-01-13 1993 01 13 1989-09-21 1989 09 21 1993-10-28 1993 10 28 1996-02-17 1996 02 17 1998-03-24 1998 03 24 1999-01-01 1999 01 01 1987-09-17 1987 09 17 1998-03-24 1998 03 24 1999-01-01 1999 01 01 1994-07-01 1994 07 01
样本表：job_history
employee_id start_date end_date job_id department_id ----------- ---------- ---------- ---------- --------- ---- 102 1993-01-13 1998-07-24 IT_PROG 60 101 1989-09-21 1993-10-27 AC_ACCOUNT 110 101 1993-10-28 1997-03-15 AC_MGR 110 201 1996-02-17 1999-12-19 MK_REP 20 114 1998-03-24 1999-12-31 ST_CLERK 50 122 1999-01-01 1999-12-31 ST_CLERK 50 200 1987-09-17 1993-06-17 AD_ASST 90 176 1998-03-24 1998-12-31 SA_REP 80 176 1999-01-01 1999-12-31 SA_MAN 80 200 1994-07-01 1998-12-31 AC_ACCOUNT 90
如果我们想从表job_history中查找在第一季度加入的员工的start_date的年，月和日值，则可以使用以下SQL。
SELECT start_date,strftime('%Y',start_date) as "Year", strftime('%m',start_date) as "Month", strftime('%d',start_date) as "Day" FROM job_history WHERE strftime('%m',start_date) IN('01','02','03');
结果
应用举例：
SELECT strftime('%Y.%m.%d %H:%M:%S','now','localtime');
结果：2020.01.01 01:41:09
其实更好的用法是这样的,比如统,本月或本年的支出:
select strftime('%Y-%m',日期) as 月份,sum(支出) as 月支出 from 流水帐 group by 月份;
SQLite包含了如下时间/日期函数：
datetime().......................产生日期和时间
date()...........................产生日期
time()...........................产生时间
strftime().......................对以上三个函数产生的日期和时间进行格式化
datetime()的用法是：datetime(日期/时间,修正符,修正符...)
date()和time()的语法与datetime()相同。
在时间/日期函数里可以使用如下格式的字符串作为参数：
YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
其中now是产生现在的时间。
举例（写这个笔记的时间是2006年10月17日晚8点到10点，测试环境：SQLite 2.8.17，WinXP，北京时间）：
例1.
select datetime('now');
结果：2006-10-17 12:55:54
例2.
select datetime('2006-10-17');
结果：2006-10-17 12:00:00
例3.
select datetime('2006-10-17 00:20:00','+1 hour','-12 minute');
结果：2006-10-17 01:08:00
例4.
select date('2006-10-17','+1 day','+1 year');
结果：2007-10-18
例5.
select datetime('now','start of year');
结果：2006-01-01 00:00:00
例6.
select datetime('now','start of month');
结果：2006-10-01 00:00:00
例7.
select datetime('now','start of day');
结果：2006-10-17 00:00:00
例8.
select datetime('now','+10 hour','start of day','+10 hour');
结果：2006-10-17 10:00:00
例9.
select datetime('now','localtime');
结果：2006-10-17 21:21:47
例10.
select datetime('now','+8 hour');
结果：2006-10-17 21:24:45
例3中的+1 hour和-12 minute表示可以在基本时间上（datetime函数的第一个参数）增加或减少一定时间。
例5中的start of year表示一年开始的时间。
从例8可以看出，尽管第2个参数加上了10个小时，但是却被第3个参数“start of day”把时间归零到00:00:00，随后的第4个参数在00:00:00
的基础上把时间增加了10个小时变成了10:00:00。
例9把格林威治时区转换成本地时区。
例10把格林威治时区转换成东八区。
strftime()函数可以把YYYY-MM-DD HH:MM:SS格式的日期字符串转换成其它形式的字符串。
strftime()的语法是strftime(格式, 日期/时间, 修正符, 修正符, ...)
它可以用以下的符号对日期和时间进行格式化：
%d 月份, 01-31
%f 小数形式的秒，SS.SSS
%H 小时, 00-23
%j 算出某一天是该年的第几天，001-366
%m 月份，00-12
%M 分钟, 00-59
%s 从1970年1月1日到现在的秒数
%S 秒, 00-59
%w 星期, 0-6 (0是星期天)
%W 算出某一天属于该年的第几周, 01-53
%Y 年, YYYY
%% 百分号
strftime()的用法举例如下：
例11.
select strftime('%Y.%m.%d %H:%M:%S','now','localtime');
结果：2006.10.17 21:41:09
例11用圆点作为日期的分隔附，并把时间转换为当地的时区的时间。
-------------------
select datetime('2015-06-10 11:17:00','+1 day','-1 hour','-10 minute','+1 year','start of day','+1 hour');
注意前面的条件会被后面的覆盖.
sqlite> select strftime('%Y-%m-%d %H:%M:%S','now','localtime');
注意格式化时要使用指定的%符号,其实相当于c里面的sprintf
存储日期和时间数据类型：
SQLite没有专门提供存储日期和时间存储类型，通常可以TEXT , REAL和INTEGER类型来替代的方式存储。
TEXT对应的数据："YYYY-MM-DD HH:MM:SS.SSS"格式的数据
REAL对应的数据： Julian日期格式存储，即从公元前 4714 年 11 月 24 日格林尼治时间的正午开始算起的天数。
INTEGER对应的数据：Unix时间形式的数据 , 即从 1970-01-01 00:00:00 UTC 算起的秒数。
可以以任何上述格式来存储日期和时间，并且可以使用内置的日期和时间函数来自由转换不同格式。
二、【算术函数】
abs(X)：返回绝对值
max(X,Y[,...])：返回最大值
min(X,Y,[,...])：返回最小值
random(*)：返回随机数
round(X[,Y])： 四舍五入
三、【字符串处理函数】
length(x) ：返回字符串字符个数
lower(x) ：大写转小写
upper(x)：小写转大写
substr(x,y,Z)：截取子串
like(A,B)：确定给定的字符串与指定的模式是否匹配
四、【条件判断函数、集合函数、其它函数】
typeof(x)：返回数据的类型
last_insert_rowid()：返回最后插入的数据的ID
************ *************************************
sqlite3提供了C函数接口来操作sqlite3数据库，其中有个关键数据结构 sqlite3 * 类型
1、打开数据库
int sqlite3_open(文件名,sqlite3 **); - 文件名若不存在，则会自动创建
返回SQLITE_OK表示操作正常，这些宏的定义在sqlite3.h文件中定义，看源代码会懂的更多
2、关闭数据库
int sqlite3_close(sqlite3 *);
3、SQL语句操作
int sqlite3_exec(sqlite3 *,const char *sql, sqlite3_callback,void *,char **errmsg);
这就是执行一条sql语句的函数
参数1：open函数得到的指针
参数2：一条sql语句，以'\0'结尾
参数3：sqlite3_callback是回调，当这条语句执行后，sqlite3会调用你提供的这个函数，回调函数要查阅资料
参数4：void *是自己提供的指针，可以传递任何指针到这里，这个参数最终会传到回调函数里面，如果不需要
传到回调函数里面，则可以设置为NULL
参数5：错误信息，当执行失败时，可以查阅这个指针，可以利用printf("%s\n",errmsg)得到一串字符串信息，
该信息表明出错的地方
通常，sqlite3_callback和void *都设置为NULL，表示不需要回调，比如做insert、delete操作，就没有必要使用回调，而当使用select时，就要使用回调，因为sqlite3把数据查出来，得通过回调来说明查出什么数据
注意sqlite3和java的整数数据类型的区别
sqlite3的数据类型和java数据类型对应上要小心，特别是整数类型。
java 中int类型4位存储，范围 -2^31到2^31-1，即-2147483648到2147483647
java 中long类型8位存储，范围-2^63到2^63-1，即--9223372036854775808到9223372036854775807
sqlite3的integer类型用1, 2, 3, 4, 6, or 8位存储。原文：
-
INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
所以java中byte，short，int，long类型表示的范围都在sqlite3的integer类型范围中。
现在可以看到sqlite3的integer不等于java的integer类型，因为表示范围不同。
如果sqlite3的integer类型表示的范围在-2147483648到2147483647间，那么java对应的用integer类型也可以，如果sqlite3的integer类型表示的范围超过了-2147483648到2147483647间，而位于-9223372036854775808到9223372036854775807间，那么java就必须用long类型表示。
Timestamp（时间戳）计算的小问题，注意将其强制转换为long。Hibernate时间段查询。
Timestamp的计算一般是先使用gettime（）将其转化为long再计算。
如下面这个例子：
Timestamp begin = new java.sql.Timestamp((new java.util.Date()).getTime());
java.sql.Timestamp end = new Timestamp(0);
long intime=begin.getTime();
long endtime=intime+((long)RemindDay*1000*24*60*60);//注意将在RemindDay前加上（long）强制类型转换。
end.setTime(endtime);
是求将当前时间加上RemindDay天后的Timestamp。在没有加强制类型转换的情况下，当Timestamp>=25时，计算就会出错。
因为“RemindDay*1000*24*60*60”都是int类型，其结果也默认是int类型，但是Timestamp>=25时其结果会超过int的最大限制。
所以加个（long）强制类型转换就可以了。
顺便提一下：
使用hibernate查询在一段时间内的数据的hpl语句是：
Timestamp begin = Timestamp.valueOf("2011-12-05 0:0:0");
Timestamp end = Timestamp.valueOf("2011-12-30 0:0:0");
String hql="from Whstock as wh where wh.endDate between '"+begin+"' and '"+end+"'";
