~drizzle-trunk/drizzle/development

drop table if exists t1,t2,t3;
select from_days(to_days("960101")),to_days(960201)-to_days("19960101"),to_days(date_add(curdate(), interval 1 day))-to_days(curdate()),weekday("1997-11-29");
from_days(to_days("960101"))	to_days(960201)-to_days("19960101")	to_days(date_add(curdate(), interval 1 day))-to_days(curdate())	weekday("1997-11-29")
1996-01-01	31	1	5
select period_add("9602",-12),period_diff(199505,"9404") ;
period_add("9602",-12)	period_diff(199505,"9404")
199502	13
select now()-now(),weekday(curdate())-weekday(now()),unix_timestamp()-unix_timestamp(now());
now()-now()	weekday(curdate())-weekday(now())	unix_timestamp()-unix_timestamp(now())
0.000000	0	0
select from_unixtime(unix_timestamp("1994-03-02 10:11:12")),from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s"),from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0;
from_unixtime(unix_timestamp("1994-03-02 10:11:12"))	from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s")	from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0
1994-03-02 10:11:12	1994-03-02 10:11:12	19940302101112.000000
select date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w");
date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w")
January Thursday 2nd 1997 97 01 02 03 04 05 4
select date_format("1997-01-02", concat("%M %W %D ","%Y %y %m %d %h %i %s %w"));
date_format("1997-01-02", concat("%M %W %D ","%Y %y %m %d %h %i %s %w"))
January Thursday 2nd 1997 97 01 02 12 00 00 4
select dayofmonth("1997-01-02"),dayofmonth(19970323);
dayofmonth("1997-01-02")	dayofmonth(19970323)
2	23
select month("1997-01-02"),year("98-02-03"),dayofyear("1997-12-31");
month("1997-01-02")	year("98-02-03")	dayofyear("1997-12-31")
1	1998	365
select month("2001-02-00"),year("2001-01-01");
ERROR HY000: Received an invalid datetime value '2001-02-00'.
select DAYOFYEAR("1997-03-03"), QUARTER(980303);
DAYOFYEAR("1997-03-03")	QUARTER(980303)
62	1
select HOUR("1997-03-03 23:03:22"), MINUTE("23:03:22"), SECOND(230322);
HOUR("1997-03-03 23:03:22")	MINUTE("23:03:22")	SECOND(230322)
23	3	22
select date_format('1998-12-31','%x-%v'),date_format('1999-01-01','%x-%v');
date_format('1998-12-31','%x-%v')	date_format('1999-01-01','%x-%v')
1998-53	1998-53
select date_format('1999-12-31','%x-%v'),date_format('2000-01-01','%x-%v');
date_format('1999-12-31','%x-%v')	date_format('2000-01-01','%x-%v')
1999-52	1999-52
select dayname("1962-03-03"),dayname("1962-03-03")+0;
dayname("1962-03-03")	dayname("1962-03-03")+0
Saturday	5
select monthname("1972-03-04"),monthname("1972-03-04")+0;
monthname("1972-03-04")	monthname("1972-03-04")+0
March	3
select time_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T');
time_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T')
00|12|0|12|00|AM|12:00:00 AM|00|00:00:00
select time_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T');
time_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T')
01|01|1|1|02|AM|01:02:03 AM|03|01:02:03
select time_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T');
time_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T')
13|01|13|1|14|PM|01:14:15 PM|15|13:14:15
select time_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T');
time_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T')
01|01|1|1|00|AM|01:00:15 AM|15|01:00:15
select date_format(concat('19980131',131415),'%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%w');
date_format(concat('19980131',131415),'%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%w')
13|01|13|1|14|PM|01:14:15 PM|15|13:14:15| January|Saturday|31st|1998|98|Sat|Jan|031|01|31|01|15|6
select date_format(19980021000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%w');
date_format(19980021000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%w')
NULL
select date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND);
date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND)
1998-01-01 00:00:00.000000
select date_add("1997-12-31 23:59:59",INTERVAL 1 MINUTE);
date_add("1997-12-31 23:59:59",INTERVAL 1 MINUTE)
1998-01-01 00:00:59.000000
select date_add("1997-12-31 23:59:59",INTERVAL 1 HOUR);
date_add("1997-12-31 23:59:59",INTERVAL 1 HOUR)
1998-01-01 00:59:59.000000
select date_add("1997-12-31 23:59:59",INTERVAL 1 DAY);
date_add("1997-12-31 23:59:59",INTERVAL 1 DAY)
1998-01-01 23:59:59.000000
select date_add("1997-12-31 23:59:59",INTERVAL 1 MONTH);
date_add("1997-12-31 23:59:59",INTERVAL 1 MONTH)
1998-01-31 23:59:59.000000
select date_add("1997-12-31 23:59:59",INTERVAL 1 YEAR);
date_add("1997-12-31 23:59:59",INTERVAL 1 YEAR)
1998-12-31 23:59:59.000000
select date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND);
date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND)
1998-01-01 00:01:00.000000
select date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE);
date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE)
1998-01-01 01:00:59.000000
select date_add("1997-12-31 23:59:59",INTERVAL "1:1" DAY_HOUR);
date_add("1997-12-31 23:59:59",INTERVAL "1:1" DAY_HOUR)
1998-01-02 00:59:59.000000
select date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH);
date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH)
1999-01-31 23:59:59.000000
select date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND);
date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND)
1998-01-01 01:01:00.000000
select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1" DAY_MINUTE);
date_add("1997-12-31 23:59:59",INTERVAL "1 1:1" DAY_MINUTE)
1998-01-02 01:00:59.000000
select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1:1" DAY_SECOND);
date_add("1997-12-31 23:59:59",INTERVAL "1 1:1:1" DAY_SECOND)
1998-01-02 01:01:00.000000
select date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND);
date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND)
1997-12-31 23:59:59.000000
select date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE);
date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE)
1997-12-31 23:59:00.000000
select date_sub("1998-01-01 00:00:00",INTERVAL 1 HOUR);
date_sub("1998-01-01 00:00:00",INTERVAL 1 HOUR)
1997-12-31 23:00:00.000000
select date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY);
date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY)
1997-12-31 00:00:00.000000
select date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH);
date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH)
1997-12-01 00:00:00.000000
select date_sub("1998-01-01 00:00:00",INTERVAL 1 YEAR);
date_sub("1998-01-01 00:00:00",INTERVAL 1 YEAR)
1997-01-01 00:00:00.000000
select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" MINUTE_SECOND);
date_sub("1998-01-01 00:00:00",INTERVAL "1:1" MINUTE_SECOND)
1997-12-31 23:58:59.000000
select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" HOUR_MINUTE);
date_sub("1998-01-01 00:00:00",INTERVAL "1:1" HOUR_MINUTE)
1997-12-31 22:59:00.000000
select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" DAY_HOUR);
date_sub("1998-01-01 00:00:00",INTERVAL "1:1" DAY_HOUR)
1997-12-30 23:00:00.000000
select date_sub("1998-01-01 00:00:00",INTERVAL "1 1" YEAR_MONTH);
date_sub("1998-01-01 00:00:00",INTERVAL "1 1" YEAR_MONTH)
1996-12-01 00:00:00.000000
select date_sub("1998-01-01 00:00:00",INTERVAL "1:1:1" HOUR_SECOND);
date_sub("1998-01-01 00:00:00",INTERVAL "1:1:1" HOUR_SECOND)
1997-12-31 22:58:59.000000
select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1" DAY_MINUTE);
date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1" DAY_MINUTE)
1997-12-30 22:59:00.000000
select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND);
date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND)
1997-12-30 22:58:59.000000
select date_add("1997-12-31 23:59:59",INTERVAL 100000 SECOND);
date_add("1997-12-31 23:59:59",INTERVAL 100000 SECOND)
1998-01-02 03:46:39.000000
select date_add("1997-12-31 23:59:59",INTERVAL -100000 MINUTE);
date_add("1997-12-31 23:59:59",INTERVAL -100000 MINUTE)
1997-10-23 13:19:59.000000
select date_add("1997-12-31 23:59:59",INTERVAL 100000 HOUR);
date_add("1997-12-31 23:59:59",INTERVAL 100000 HOUR)
2009-05-29 15:59:59.000000
select date_add("1997-12-31 23:59:59",INTERVAL -100000 DAY);
date_add("1997-12-31 23:59:59",INTERVAL -100000 DAY)
1724-03-17 23:59:59.000000
select date_add("1997-12-31 23:59:59",INTERVAL 100000 MONTH);
date_add("1997-12-31 23:59:59",INTERVAL 100000 MONTH)
NULL
Warnings:
Warning	1441	Datetime function: datetime field overflow
select date_add("1997-12-31 23:59:59",INTERVAL -100000 YEAR);
date_add("1997-12-31 23:59:59",INTERVAL -100000 YEAR)
NULL
Warnings:
Warning	1441	Datetime function: datetime field overflow
select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND);
date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND)
1998-01-07 22:40:00.000000
select date_add("1997-12-31 23:59:59",INTERVAL "-10000:1" HOUR_MINUTE);
date_add("1997-12-31 23:59:59",INTERVAL "-10000:1" HOUR_MINUTE)
1996-11-10 07:58:59.000000
select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" DAY_HOUR);
date_add("1997-12-31 23:59:59",INTERVAL "10000:1" DAY_HOUR)
2025-05-19 00:59:59.000000
select date_add("1997-12-31 23:59:59",INTERVAL "-100 1" YEAR_MONTH);
date_add("1997-12-31 23:59:59",INTERVAL "-100 1" YEAR_MONTH)
1897-11-30 23:59:59.000000
select date_add("1997-12-31 23:59:59",INTERVAL "10000:99:99" HOUR_SECOND);
date_add("1997-12-31 23:59:59",INTERVAL "10000:99:99" HOUR_SECOND)
1999-02-21 17:40:38.000000
select date_add("1997-12-31 23:59:59",INTERVAL " -10000 99:99" DAY_MINUTE);
date_add("1997-12-31 23:59:59",INTERVAL " -10000 99:99" DAY_MINUTE)
1970-08-11 19:20:59.000000
select date_add("1997-12-31 23:59:59",INTERVAL "10000 99:99:99" DAY_SECOND);
date_add("1997-12-31 23:59:59",INTERVAL "10000 99:99:99" DAY_SECOND)
2025-05-23 04:40:38.000000
select "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
"1997-12-31 23:59:59" + INTERVAL 1 SECOND
1998-01-01 00:00:00.000000
select INTERVAL 1 DAY + "1997-12-31";
INTERVAL 1 DAY + "1997-12-31"
1998-01-01
select "1998-01-01 00:00:00" - INTERVAL 1 SECOND;
"1998-01-01 00:00:00" - INTERVAL 1 SECOND
1997-12-31 23:59:59.000000
select date_sub("1998-01-02",INTERVAL 31 DAY);
date_sub("1998-01-02",INTERVAL 31 DAY)
1997-12-02
select date_add("1997-12-31",INTERVAL 1 SECOND);
date_add("1997-12-31",INTERVAL 1 SECOND)
1997-12-31 00:00:01.000000
select date_add("1997-12-31",INTERVAL 1 DAY);
date_add("1997-12-31",INTERVAL 1 DAY)
1998-01-01
select date_add(NULL,INTERVAL 100000 SECOND);
date_add(NULL,INTERVAL 100000 SECOND)
NULL
select date_add("1997-12-31 23:59:59",INTERVAL NULL SECOND);
date_add("1997-12-31 23:59:59",INTERVAL NULL SECOND)
NULL
select date_add("1997-12-31 23:59:59",INTERVAL NULL MINUTE_SECOND);
date_add("1997-12-31 23:59:59",INTERVAL NULL MINUTE_SECOND)
NULL
select date_add("9999-12-31 23:59:59",INTERVAL 1 SECOND);
date_add("9999-12-31 23:59:59",INTERVAL 1 SECOND)
NULL
Warnings:
Warning	1441	Datetime function: datetime field overflow
select date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND);
date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND)
NULL
Warnings:
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00'
select date_add('1998-01-30',Interval 1 month);
date_add('1998-01-30',Interval 1 month)
1998-02-28
select date_add('1998-01-30',Interval '2:1' year_month);
date_add('1998-01-30',Interval '2:1' year_month)
2000-02-29
select date_add('1996-02-29',Interval '1' year);
date_add('1996-02-29',Interval '1' year)
1997-02-28
select extract(YEAR FROM "1999-01-02 10:11:12");
extract(YEAR FROM "1999-01-02 10:11:12")
1999
select extract(YEAR_MONTH FROM "1999-01-02");
extract(YEAR_MONTH FROM "1999-01-02")
199901
select extract(DAY FROM "1999-01-02");
extract(DAY FROM "1999-01-02")
2
select extract(DAY_HOUR FROM "1999-01-02 10:11:12");
extract(DAY_HOUR FROM "1999-01-02 10:11:12")
210
select extract(DAY_MINUTE FROM "02 10:11:12");
ERROR HY000: Received an invalid datetime value '02 10:11:12'.
select extract(DAY_SECOND FROM "225 10:11:12");
ERROR HY000: Received an invalid datetime value '225 10:11:12'.
select extract(HOUR FROM "1999-01-02 10:11:12");
extract(HOUR FROM "1999-01-02 10:11:12")
10
select extract(HOUR_MINUTE FROM "10:11:12");
extract(HOUR_MINUTE FROM "10:11:12")
1011
select extract(HOUR_SECOND FROM "10:11:12");
extract(HOUR_SECOND FROM "10:11:12")
101112
select extract(MINUTE FROM "10:11:12");
extract(MINUTE FROM "10:11:12")
11
select extract(MINUTE_SECOND FROM "10:11:12");
extract(MINUTE_SECOND FROM "10:11:12")
1112
select extract(SECOND FROM "1999-01-02 10:11:12");
extract(SECOND FROM "1999-01-02 10:11:12")
12
select extract(MONTH FROM "2001-02-00");
ERROR HY000: Received an invalid datetime value '2001-02-00'.
SELECT EXTRACT(QUARTER FROM '2004-01-15') AS quarter;
quarter
1
SELECT EXTRACT(QUARTER FROM '2004-02-15') AS quarter;
quarter
1
SELECT EXTRACT(QUARTER FROM '2004-03-15') AS quarter;
quarter
1
SELECT EXTRACT(QUARTER FROM '2004-04-15') AS quarter;
quarter
2
SELECT EXTRACT(QUARTER FROM '2004-05-15') AS quarter;
quarter
2
SELECT EXTRACT(QUARTER FROM '2004-06-15') AS quarter;
quarter
2
SELECT EXTRACT(QUARTER FROM '2004-07-15') AS quarter;
quarter
3
SELECT EXTRACT(QUARTER FROM '2004-08-15') AS quarter;
quarter
3
SELECT EXTRACT(QUARTER FROM '2004-09-15') AS quarter;
quarter
3
SELECT EXTRACT(QUARTER FROM '2004-10-15') AS quarter;
quarter
4
SELECT EXTRACT(QUARTER FROM '2004-11-15') AS quarter;
quarter
4
SELECT EXTRACT(QUARTER FROM '2004-12-15') AS quarter;
quarter
4
SELECT "1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND;
"1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND
1968-01-20 03:14:08.000000
SELECT "1900-01-01 00:00:00" + INTERVAL "1:2147483647" MINUTE_SECOND;
"1900-01-01 00:00:00" + INTERVAL "1:2147483647" MINUTE_SECOND
1968-01-20 03:15:07.000000
SELECT "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND;
"1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND
8895-03-27 22:11:40.000000
SELECT "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND;
"1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND
NULL
Warnings:
Warning	1441	Datetime function: datetime field overflow
create table t1 (ctime varchar(20));
insert into t1 values ('2001-01-12 12:23:40');
select ctime, hour(ctime) from t1;
ctime	hour(ctime)
2001-01-12 12:23:40	12
select ctime from t1 where extract(MONTH FROM ctime) = 1 AND extract(YEAR FROM ctime) = 2001;
ctime
2001-01-12 12:23:40
drop table t1;
create table t1 (id int);
create table t2 (id int, date date);
insert into t1 values (1);
insert into t2 values (1, NULL);
insert into t1 values (2);
insert into t2 values (2, "2000-01-01");
select monthname(date) from t1 inner join t2 on t1.id = t2.id;
monthname(date)
NULL
NULL
select monthname(date) from t1 inner join t2 on t1.id = t2.id order by t1.id;
monthname(date)
NULL
NULL
drop table t1,t2;
CREATE TEMPORARY TABLE t1 (updated text) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('');
SELECT month(updated) from t1;
ERROR HY000: Received an invalid datetime value ''.
SELECT year(updated) from t1;
ERROR HY000: Received an invalid datetime value ''.
drop table t1;
create table t1 (d date, dt datetime, t timestamp, c char(10));
insert into t1 values (null, null, null, null);
insert into t1 values ("0000-00-00", "0000-00-00", "0000-00-00", "0000-00-00");
ERROR HY000: Received an invalid DATE value '0000-00-00'.
select dayofyear("0000-00-00"),dayofyear(d),dayofyear(dt),dayofyear(t),dayofyear(c) from t1;
ERROR HY000: Received an invalid datetime value '0000-00-00'.
select dayofmonth("0000-00-00"),dayofmonth(d),dayofmonth(dt),dayofmonth(t),dayofmonth(c) from t1;
ERROR HY000: Received an invalid datetime value '0000-00-00'.
select month("0000-00-00"),month(d),month(dt),month(t),month(c) from t1;
ERROR HY000: Received an invalid datetime value '0000-00-00'.
select quarter("0000-00-00"),quarter(d),quarter(dt),quarter(t),quarter(c) from t1;
ERROR HY000: Received an invalid datetime value '0000-00-00'.
select year("0000-00-00"),year(d),year(dt),year(t),year(c) from t1;
ERROR HY000: Received an invalid datetime value '0000-00-00'.
select to_days("0000-00-00"),to_days(d),to_days(dt),to_days(c) from t1;
ERROR HY000: Received an invalid datetime value '0000-00-00'.
select extract(MONTH FROM "0000-00-00"),extract(MONTH FROM d),extract(MONTH FROM dt),extract(MONTH FROM t),extract(MONTH FROM c) from t1;
ERROR HY000: Received an invalid datetime value '0000-00-00'.
drop table t1;
CREATE TABLE t1 ( start datetime default NULL);
INSERT INTO t1 VALUES ('2002-10-21 00:00:00'),('2002-10-28 00:00:00'),('2002-11-04 00:00:00');
CREATE TABLE t2 ( ctime1 timestamp NOT NULL, ctime2 timestamp NOT NULL);
INSERT INTO t2 VALUES (20021029165106,20021105164731);
CREATE TABLE t3 (ctime1 char(19) NOT NULL, ctime2 char(19) NOT NULL);
INSERT INTO t3 VALUES ("2002-10-29 16:51:06","2002-11-05 16:47:31");
select * from t1, t2 where t1.start between t2.ctime1 and t2.ctime2;
start	ctime1	ctime2
2002-11-04 00:00:00	2002-10-29 16:51:06	2002-11-05 16:47:31
select * from t1, t2 where t1.start >= t2.ctime1 and t1.start <= t2.ctime2;
start	ctime1	ctime2
2002-11-04 00:00:00	2002-10-29 16:51:06	2002-11-05 16:47:31
select * from t1, t3 where t1.start between t3.ctime1 and t3.ctime2;
start	ctime1	ctime2
2002-11-04 00:00:00	2002-10-29 16:51:06	2002-11-05 16:47:31
drop table t1,t2,t3;
select @a:=FROM_UNIXTIME(1);
@a:=FROM_UNIXTIME(1)
1970-01-01 00:00:01.000000
select unix_timestamp(@a);
unix_timestamp(@a)
1
select unix_timestamp('1970-01-01 00:00:01');
unix_timestamp('1970-01-01 00:00:01')
1
select from_unixtime(-1);
from_unixtime(-1)
NULL
select from_unixtime(2147483647);
from_unixtime(2147483647)
2038-01-19 03:14:07
select from_unixtime(2147483648);
from_unixtime(2147483648)
NULL
select from_unixtime(0);
from_unixtime(0)
1970-01-01 00:00:00
select unix_timestamp(from_unixtime(2147483647));
unix_timestamp(from_unixtime(2147483647))
2147483647
select unix_timestamp(from_unixtime(2147483648));
unix_timestamp(from_unixtime(2147483648))
NULL
select unix_timestamp('2039-01-20 01:00:00');
ERROR HY000: Received an invalid value '2039-01-20 01:00:00' for a UNIX timestamp.
select unix_timestamp('1968-01-20 01:00:00');
ERROR HY000: Received an invalid value '1968-01-20 01:00:00' for a UNIX timestamp.
select unix_timestamp('2038-02-10 01:00:00');
ERROR HY000: Received an invalid value '2038-02-10 01:00:00' for a UNIX timestamp.
select unix_timestamp('1969-11-20 01:00:00');
ERROR HY000: Received an invalid value '1969-11-20 01:00:00' for a UNIX timestamp.
select unix_timestamp('2038-01-20 01:00:00');
ERROR HY000: Received an invalid value '2038-01-20 01:00:00' for a UNIX timestamp.
select unix_timestamp('1969-12-30 01:00:00');
ERROR HY000: Received an invalid value '1969-12-30 01:00:00' for a UNIX timestamp.
select unix_timestamp('2038-01-17 12:00:00');
unix_timestamp('2038-01-17 12:00:00')
2147342400
select unix_timestamp('2038-01-19 03:14:07');
unix_timestamp('2038-01-19 03:14:07')
2147483647
CREATE TABLE t1 (datetime_arg datetime, timestamp_arg timestamp, date_arg date);
INSERT INTO t1 values ("2001-01-02 03:04:05", "2002-01-02 03:04:05", "2003-01-02");
SELECT * from t1;
datetime_arg	timestamp_arg	date_arg
2001-01-02 03:04:05	2002-01-02 03:04:05	2003-01-02
select date_add("1997-12-31",INTERVAL 1 SECOND);
date_add("1997-12-31",INTERVAL 1 SECOND)
1997-12-31 00:00:01.000000
select date_add("1997-12-31",INTERVAL "1 1" YEAR_MONTH);
date_add("1997-12-31",INTERVAL "1 1" YEAR_MONTH)
1999-01-31
select date_add(datetime_arg, INTERVAL 1 SECOND) from t1;
date_add(datetime_arg, INTERVAL 1 SECOND)
2001-01-02 03:04:06
select date_add(datetime_arg, INTERVAL 1 YEAR) from t1;
date_add(datetime_arg, INTERVAL 1 YEAR)
2002-01-02 03:04:05
select date_add(date_arg, INTERVAL 1 SECOND) from t1;
date_add(date_arg, INTERVAL 1 SECOND)
2003-01-02 00:00:01
select date_add(date_arg, INTERVAL 1 MINUTE) from t1;
date_add(date_arg, INTERVAL 1 MINUTE)
2003-01-02 00:01:00
select date_add(date_arg, INTERVAL 1 HOUR) from t1;
date_add(date_arg, INTERVAL 1 HOUR)
2003-01-02 01:00:00
select date_add(date_arg, INTERVAL 1 DAY) from t1;
date_add(date_arg, INTERVAL 1 DAY)
2003-01-03
select date_add(date_arg, INTERVAL 1 MONTH) from t1;
date_add(date_arg, INTERVAL 1 MONTH)
2003-02-02
select date_add(date_arg, INTERVAL 1 YEAR) from t1;
date_add(date_arg, INTERVAL 1 YEAR)
2004-01-02
select date_add(date_arg, INTERVAL "1:1" MINUTE_SECOND) from t1;
date_add(date_arg, INTERVAL "1:1" MINUTE_SECOND)
2003-01-02 00:01:01
select date_add(date_arg, INTERVAL "1:1" HOUR_MINUTE) from t1;
date_add(date_arg, INTERVAL "1:1" HOUR_MINUTE)
2003-01-02 01:01:00
select date_add(date_arg, INTERVAL "1:1" DAY_HOUR) from t1;
date_add(date_arg, INTERVAL "1:1" DAY_HOUR)
2003-01-03 01:00:00
select date_add(date_arg, INTERVAL "1 1" YEAR_MONTH) from t1;
date_add(date_arg, INTERVAL "1 1" YEAR_MONTH)
2004-02-02
select date_add(date_arg, INTERVAL "1:1:1" HOUR_SECOND) from t1;
date_add(date_arg, INTERVAL "1:1:1" HOUR_SECOND)
2003-01-02 01:01:01
select date_add(date_arg, INTERVAL "1 1:1" DAY_MINUTE) from t1;
date_add(date_arg, INTERVAL "1 1:1" DAY_MINUTE)
2003-01-03 01:01:00
select date_add(date_arg, INTERVAL "1 1:1:1" DAY_SECOND) from t1;
date_add(date_arg, INTERVAL "1 1:1:1" DAY_SECOND)
2003-01-03 01:01:01
select date_add(date_arg, INTERVAL "1" WEEK) from t1;
date_add(date_arg, INTERVAL "1" WEEK)
2003-01-09
select date_add(date_arg, INTERVAL "1" QUARTER) from t1;
date_add(date_arg, INTERVAL "1" QUARTER)
2003-04-02
select timestampadd(MINUTE, 1, date_arg) from t1;
timestampadd(MINUTE, 1, date_arg)
2003-01-02 00:01:00
select timestampadd(WEEK, 1, date_arg) from t1;
timestampadd(WEEK, 1, date_arg)
2003-01-09
select timestampadd(SQL_TSI_SECOND, 1, date_arg) from t1;
timestampadd(SQL_TSI_SECOND, 1, date_arg)
2003-01-02 00:00:01
select timestampadd(SQL_TSI_FRAC_SECOND, 1, date_arg) from t1;
timestampadd(SQL_TSI_FRAC_SECOND, 1, date_arg)
2003-01-02 00:00:00.000001
select timestampdiff(MONTH, '2001-02-01', '2001-05-01') as a;
a
3
select timestampdiff(YEAR, '2002-05-01', '2001-01-01') as a;
a
-1
select timestampdiff(QUARTER, '2002-05-01', '2001-01-01') as a;
a
-5
select timestampdiff(MONTH, '2000-03-28', '2000-02-29') as a;
a
0
select timestampdiff(MONTH, '1991-03-28', '2000-02-29') as a;
a
107
select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a;
a
12
select timestampdiff(SQL_TSI_HOUR, '2001-02-01', '2001-05-01') as a;
a
2136
select timestampdiff(SQL_TSI_DAY, '2001-02-01', '2001-05-01') as a;
a
89
select timestampdiff(SQL_TSI_MINUTE, '2001-02-01 12:59:59', '2001-05-01 12:58:59') as a;
a
128159
select timestampdiff(SQL_TSI_SECOND, '2001-02-01 12:59:59', '2001-05-01 12:58:58') as a;
a
7689539
select timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12:58:58.119999') as a;
a
7689538999999
select timestampdiff(SQL_TSI_DAY, '1986-02-01', '1986-03-01') as a1,
timestampdiff(SQL_TSI_DAY, '1900-02-01', '1900-03-01') as a2,
timestampdiff(SQL_TSI_DAY, '1996-02-01', '1996-03-01') as a3,
timestampdiff(SQL_TSI_DAY, '2000-02-01', '2000-03-01') as a4;
a1	a2	a3	a4
28	28	29	29
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:27');
TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:27')
0
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:28');
TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:28')
1
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:29');
TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:29')
1
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:27');
TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:27')
1
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:28');
TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:28')
2
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:29');
TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:29')
2
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:27');
TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:27')
0
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:28');
TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:28')
1
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:29');
TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:29')
1
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:27');
TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:27')
1
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:28');
TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:28')
2
SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:29');
TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:29')
2
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:27');
TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:27')
0
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:28');
TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:28')
1
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:29');
TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:29')
1
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:27');
TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:27')
1
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:28');
TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:28')
2
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:29');
TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:29')
2
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:27');
TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:27')
0
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:28');
TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:28')
1
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:29');
TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:29')
1
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:27');
TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:27')
1
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:28');
TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:28')
2
SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:29');
TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:29')
2
drop table t1;
select last_day('2000-02-05');
last_day('2000-02-05')
2000-02-29
select last_day('2002-12-31');
last_day('2002-12-31')
2002-12-31
select last_day('2003-03-32');
ERROR HY000: Received an invalid datetime value '2003-03-32'.
select last_day('2003-04-01');
last_day('2003-04-01')
2003-04-30
select last_day('2001-01-01 01:01:01');
last_day('2001-01-01 01:01:01')
2001-01-31
select last_day(NULL);
last_day(NULL)
NULL
select last_day('2001-02-12');
last_day('2001-02-12')
2001-02-28
create table t1 select last_day('2000-02-05') as a,
from_days(to_days("960101")) as b;
describe t1;
Field	Type	Null	Default	Default_is_NULL	On_Update
a	DATE	NO		NO	
b	DATE	YES		YES	
select * from t1;
a	b
2000-02-29	1996-01-01
drop table t1;
select last_day('2000-02-05') as a,
from_days(to_days("960101")) as b;
a	b
2000-02-29	1996-01-01
select date_add(last_day("1997-12-1"), INTERVAL 1 DAY);
date_add(last_day("1997-12-1"), INTERVAL 1 DAY)
1998-01-01
select length(last_day("1997-12-1"));
length(last_day("1997-12-1"))
10
select last_day("1997-12-1")+0;
last_day("1997-12-1")+0
19971231
select last_day("1997-12-1")+0.0;
last_day("1997-12-1")+0.0
19971231.0
select strcmp(date_sub(localtimestamp(), interval 0 hour), utc_timestamp())=0;
strcmp(date_sub(localtimestamp(), interval 0 hour), utc_timestamp())=0
1
select strcmp(date_format(date_sub(localtimestamp(), interval 0 hour),"%Y-%m-%d"), utc_date())=0;
strcmp(date_format(date_sub(localtimestamp(), interval 0 hour),"%Y-%m-%d"), utc_date())=0
1
select strcmp(date_format(utc_timestamp(),"%Y-%m-%d"), utc_date())=0;
strcmp(date_format(utc_timestamp(),"%Y-%m-%d"), utc_date())=0
1
explain extended select period_add("9602",-12),period_diff(199505,"9404"),from_days(to_days("960101")),dayofmonth("1997-01-02"), month("1997-01-02"), monthname("1972-03-04"),dayofyear("0000-00-00"),HOUR("1997-03-03 23:03:22"),MINUTE("23:03:22"),SECOND(230322),QUARTER(980303),weekday(curdate())-weekday(now()),dayname("1962-03-03"),unix_timestamp(),curdate(),utc_date(),utc_timestamp(),date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w"),from_unixtime(unix_timestamp("1994-03-02 10:11:12")),"1997-12-31 23:59:59" + INTERVAL 1 SECOND,"1998-01-01 00:00:00" - INTERVAL 1 SECOND,INTERVAL 1 DAY + "1997-12-31", extract(YEAR FROM "1999-01-02 10:11:12"),date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select period_add('9602',-(12)) AS `period_add("9602",-12)`,period_diff(199505,'9404') AS `period_diff(199505,"9404")`,from_days(to_days('960101')) AS `from_days(to_days("960101"))`,dayofmonth('1997-01-02') AS `dayofmonth("1997-01-02")`,month('1997-01-02') AS `month("1997-01-02")`,monthname('1972-03-04') AS `monthname("1972-03-04")`,dayofyear('0000-00-00') AS `dayofyear("0000-00-00")`,hour('1997-03-03 23:03:22') AS `HOUR("1997-03-03 23:03:22")`,minute('23:03:22') AS `MINUTE("23:03:22")`,second(230322) AS `SECOND(230322)`,quarter(980303) AS `QUARTER(980303)`,(weekday(curdate()) - weekday(now())) AS `weekday(curdate())-weekday(now())`,dayname('1962-03-03') AS `dayname("1962-03-03")`,unix_timestamp() AS `unix_timestamp()`,curdate() AS `curdate()`,utc_date() AS `utc_date()`,utc_timestamp() AS `utc_timestamp()`,date_format('1997-01-02 03:04:05','%M %W %D %Y %y %m %d %h %i %s %w') AS `date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w")`,from_unixtime(unix_timestamp('1994-03-02 10:11:12')) AS `from_unixtime(unix_timestamp("1994-03-02 10:11:12"))`,('1997-12-31 23:59:59' + interval 1 second) AS `"1997-12-31 23:59:59" + INTERVAL 1 SECOND`,('1998-01-01 00:00:00' - interval 1 second) AS `"1998-01-01 00:00:00" - INTERVAL 1 SECOND`,('1997-12-31' + interval 1 day) AS `INTERVAL 1 DAY + "1997-12-31"`,extract(year from '1999-01-02 10:11:12') AS `extract(YEAR FROM "1999-01-02 10:11:12")`,('1997-12-31 23:59:59' + interval 1 second) AS `date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND)`
SET @TMP='2007-08-01 12:22:49';
CREATE TABLE t1 (d DATETIME);
INSERT INTO t1 VALUES ('2007-08-01 12:22:59');
INSERT INTO t1 VALUES ('2007-08-01 12:23:01');
INSERT INTO t1 VALUES ('2007-08-01 12:23:20');
SELECT count(*) FROM t1 WHERE d>FROM_DAYS(TO_DAYS(@TMP)) AND d<=FROM_DAYS(TO_DAYS(@TMP)+1);
count(*)
3
DROP TABLE t1;
select last_day('2005-00-00');
ERROR HY000: Received an invalid datetime value '2005-00-00'.
select last_day('2005-00-01');
ERROR HY000: Received an invalid datetime value '2005-00-01'.
select last_day('2005-01-00');
ERROR HY000: Received an invalid datetime value '2005-01-00'.
create table t1(f1 date, f3 datetime);
insert into t1 values ("2006-01-01", "2006-01-01 12:01:01");
insert into t1 values ("2006-01-02", "2006-01-02 12:01:02");
select f1 from t1 where f1 between CAST("2006-1-1" as date) and CAST(20060101 as date);
f1
2006-01-01
select f1 from t1 where f1 between cast("2006-1-1" as date) and cast("2006.1.1" as date);
f1
2006-01-01
select f1 from t1 where date(f1) between cast("2006-1-1" as date) and cast("2006.1.1" as date);
f1
2006-01-01
select f3 from t1 where f3 between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime);
f3
2006-01-01 12:01:01
select f3 from t1 where timestamp(f3) between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime);
f3
2006-01-01 12:01:01
select f1 from t1 where cast("2006-1-1" as date) between f1 and f3;
f1
2006-01-01
select f1 from t1 where cast("2006-1-1" as date) between date(f1) and date(f3);
f1
2006-01-01
select f1 from t1 where cast("2006-1-1" as date) between f1 and cast('zzz' as date);
f1
Warnings:
Warning	1292	Incorrect datetime value: 'zzz'
select f1 from t1 where makedate(2006,1) between date(f1) and date(f3);
f1
2006-01-01
select f1 from t1 where makedate(2006,2) between date(f1) and date(f3);
f1
2006-01-02
drop table t1;
SELECT EXTRACT(HOUR FROM '100000:02:03');
ERROR HY000: Received an invalid datetime value '100000:02:03'.
SHOW VARIABLES LIKE 'character_set_results';
Variable_name	Value
CREATE TABLE testBug8868 (field1 DATE, field2 VARCHAR(32));
INSERT INTO testBug8868 VALUES ('2006-09-04', 'abcd');
SELECT DATE_FORMAT(field1,'%b-%e %l:%i%p') as fmtddate, field2 FROM testBug8868;
fmtddate	field2
Sep-4 12:00AM	abcd
DROP TABLE testBug8868;
select last_day('0000-00-00');
ERROR HY000: Received an invalid datetime value '0000-00-00'.
End of 4.1 tests
explain extended select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a1,
timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12:58:58.119999') as a2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select timestampdiff(WEEK,'2001-02-01','2001-05-01') AS `a1`,timestampdiff(SECOND_FRAC,'2001-02-01 12:59:59.120000','2001-05-01 12:58:58.119999') AS `a2`
select time_format('100:00:00', '%H %k %h %I %l');
time_format('100:00:00', '%H %k %h %I %l')
100 100 04 04 4
select timestampdiff(month,'2004-09-11','2004-09-11');
timestampdiff(month,'2004-09-11','2004-09-11')
0
select timestampdiff(month,'2004-09-11','2005-09-11');
timestampdiff(month,'2004-09-11','2005-09-11')
12
select timestampdiff(month,'2004-09-11','2006-09-11');
timestampdiff(month,'2004-09-11','2006-09-11')
24
select timestampdiff(month,'2004-09-11','2007-09-11');
timestampdiff(month,'2004-09-11','2007-09-11')
36
select timestampdiff(month,'2005-09-11','2004-09-11');
timestampdiff(month,'2005-09-11','2004-09-11')
-12
select timestampdiff(month,'2005-09-11','2003-09-11');
timestampdiff(month,'2005-09-11','2003-09-11')
-24
select timestampdiff(month,'2004-02-28','2005-02-28');
timestampdiff(month,'2004-02-28','2005-02-28')
12
select timestampdiff(month,'2004-02-29','2005-02-28');
timestampdiff(month,'2004-02-29','2005-02-28')
11
select timestampdiff(month,'2004-02-28','2005-02-28');
timestampdiff(month,'2004-02-28','2005-02-28')
12
select timestampdiff(month,'2004-03-29','2005-03-28');
timestampdiff(month,'2004-03-29','2005-03-28')
11
select timestampdiff(month,'2003-02-28','2004-02-29');
timestampdiff(month,'2003-02-28','2004-02-29')
12
select timestampdiff(month,'2003-02-28','2005-02-28');
timestampdiff(month,'2003-02-28','2005-02-28')
24
select timestampdiff(month,'1999-09-11','2001-10-10');
timestampdiff(month,'1999-09-11','2001-10-10')
24
select timestampdiff(month,'1999-09-11','2001-9-11');
timestampdiff(month,'1999-09-11','2001-9-11')
24
select timestampdiff(year,'1999-09-11','2001-9-11');
timestampdiff(year,'1999-09-11','2001-9-11')
2
select timestampdiff(year,'2004-02-28','2005-02-28');
timestampdiff(year,'2004-02-28','2005-02-28')
1
select timestampdiff(year,'2004-02-29','2005-02-28');
timestampdiff(year,'2004-02-29','2005-02-28')
0
CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, day date);
CREATE TABLE t2 (id int NOT NULL PRIMARY KEY, day date);
INSERT INTO t1 VALUES
(1, '2005-06-01'), (2, '2005-02-01'), (3, '2005-07-01');
INSERT INTO t2 VALUES
(1, '2005-08-01'), (2, '2005-06-15'), (3, '2005-07-15');
SELECT * FROM t1, t2 
WHERE t1.day BETWEEN 
'2005.09.01' - INTERVAL 6 MONTH AND t2.day;
id	day	id	day
1	2005-06-01	1	2005-08-01
1	2005-06-01	2	2005-06-15
1	2005-06-01	3	2005-07-15
3	2005-07-01	1	2005-08-01
3	2005-07-01	3	2005-07-15
SELECT * FROM t1, t2 
WHERE CAST(t1.day AS DATE) BETWEEN 
'2005.09.01' - INTERVAL 6 MONTH AND t2.day;
id	day	id	day
1	2005-06-01	1	2005-08-01
1	2005-06-01	2	2005-06-15
1	2005-06-01	3	2005-07-15
3	2005-07-01	1	2005-08-01
3	2005-07-01	3	2005-07-15
DROP TABLE t1,t2;
create table t1 (field DATE);
insert into t1 values ('2006-11-06');
select * from t1 where field < '2006-11-06 04:08:36.0';
field
2006-11-06
select * from t1 where field = '2006-11-06 04:08:36.0';
field
select * from t1 where field = '2006-11-06';
field
2006-11-06
select * from t1 where CAST(field as DATETIME) < '2006-11-06 04:08:36.0';
field
2006-11-06
select * from t1 where CAST(field as DATE) < '2006-11-06 04:08:36.0';
field
2006-11-06
drop table t1;
select DATE_ADD('20071108181000', INTERVAL 1 DAY);
DATE_ADD('20071108181000', INTERVAL 1 DAY)
2007-11-09 18:10:00.000000
select DATE_ADD(20071108181000,   INTERVAL 1 DAY);
DATE_ADD(20071108181000,   INTERVAL 1 DAY)
2007-11-09 18:10:00.000000
select DATE_ADD('20071108',       INTERVAL 1 DAY);
DATE_ADD('20071108',       INTERVAL 1 DAY)
2007-11-09
select DATE_ADD(20071108,         INTERVAL 1 DAY);
DATE_ADD(20071108,         INTERVAL 1 DAY)
2007-11-09
select LAST_DAY('2007-12-06 08:59:19.05') - INTERVAL 1 SECOND;
LAST_DAY('2007-12-06 08:59:19.05') - INTERVAL 1 SECOND
2007-12-30 23:59:59
SELECT TIMESTAMPADD(FRAC_SECOND, 1, '2008-02-18');
TIMESTAMPADD(FRAC_SECOND, 1, '2008-02-18')
2008-02-18 00:00:00.000001
SELECT TIMESTAMPDIFF(FRAC_SECOND, '2008-02-17', '2008-02-18');
TIMESTAMPDIFF(FRAC_SECOND, '2008-02-17', '2008-02-18')
86400000000
SELECT DATE_ADD('2008-02-18', INTERVAL 1 FRAC_SECOND);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'FRAC_SECOND)' at line 1
SELECT DATE_SUB('2008-02-18', INTERVAL 1 FRAC_SECOND);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'FRAC_SECOND)' at line 1
SELECT '2008-02-18' + INTERVAL 1 FRAC_SECOND;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'FRAC_SECOND' at line 1
SELECT '2008-02-18' - INTERVAL 1 FRAC_SECOND;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'FRAC_SECOND' at line 1
End of 5.0 tests
select date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND)
0049-12-31 23:59:59.000000
select date_sub("0199-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("0199-01-01 00:00:01",INTERVAL 2 SECOND)
0198-12-31 23:59:59.000000
select date_add("0199-12-31 23:59:59",INTERVAL 2 SECOND);
date_add("0199-12-31 23:59:59",INTERVAL 2 SECOND)
0200-01-01 00:00:01.000000
select date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND)
0199-12-31 23:59:59.000000
select date_sub("0200-01-01 00:00:01",INTERVAL 1 SECOND);
date_sub("0200-01-01 00:00:01",INTERVAL 1 SECOND)
0200-01-01 00:00:00.000000
select date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND)
0199-12-31 23:59:59.000000
select date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR);
date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR)
0001-01-01 23:59:59.000000
select date_sub("50-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("50-01-01 00:00:01",INTERVAL 2 SECOND)
2049-12-31 23:59:59.000000
select date_sub("90-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("90-01-01 00:00:01",INTERVAL 2 SECOND)
1989-12-31 23:59:59.000000
select date_sub("0069-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("0069-01-01 00:00:01",INTERVAL 2 SECOND)
0068-12-31 23:59:59.000000
select date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND)
0168-12-31 23:59:59.000000
End of 5.1 tests