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 TABLEt

(

ts1TIMESTAMP NULL DEFAULT NULL,

ts2TIMESTAMP NULL DEFAULT 0,

ts3TIMESTAMP 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也支持如下的数据类型smallint16位整数integer32位整数decimal(p,s) p是精确值,s是小数位数float32位实数double64位实数char(n) n长度字符串,不能超过254varchar(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”FROMjob_historyWHERE strftime(‘%m’,start_date)IN(’01’,’02’,’03’);

结果

start_date年月日———- ———- ———- ——

1993-01-13 1993 01 13

1996-02-17 1996 02 17

1998-03-24 1998 03 24

1999-01-01 1999 01 01

1998-03-24 1998 03 24

1999-01-01 1999 01 01

应用举例:

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数据类型对应上要小心,特别是整数类型。

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的计算一般是先使用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+”‘”;


版权声明:本文为weixin_39836860原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
原文链接:https://blog.csdn.net/weixin_39836860/article/details/111842376