func_time.test 13.1 KB
Newer Older
1 2 3
#
# time functions
#
4
drop table if exists t1,t2,t3;
5

6 7 8 9
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");
select period_add("9602",-12),period_diff(199505,"9404") ;
select now()-now(),weekday(curdate())-weekday(now()),unix_timestamp()-unix_timestamp(now());
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;
10 11
select sec_to_time(9001),sec_to_time(9001)+0,time_to_sec("15:12:22"),
  sec_to_time(time_to_sec("0:30:47")/6.21);
12
select sec_to_time(time_to_sec('-838:59:59'));
13 14
select now()-curdate()*1000000-curtime();
select strcmp(current_timestamp(),concat(current_date()," ",current_time()));
15 16
select strcmp(localtime(),concat(current_date()," ",current_time()));
select strcmp(localtimestamp(),concat(current_date()," ",current_time()));
17 18 19 20
select date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w");
select date_format("1997-01-02", concat("%M %W %D ","%Y %y %m %d %h %i %s %w"));
select dayofmonth("1997-01-02"),dayofmonth(19970323);
select month("1997-01-02"),year("98-02-03"),dayofyear("1997-12-31");
21
select month("2001-02-00"),year("2001-00-00");
22 23
select DAYOFYEAR("1997-03-03"), WEEK("1998-03-03"), QUARTER(980303);
select HOUR("1997-03-03 23:03:22"), MINUTE("23:03:22"), SECOND(230322);
24 25

# Test of week and yearweek
26 27 28 29
select week(19980101),week(19970101),week(19980101,1),week(19970101,1);
select week(19981231),week(19971231),week(19981231,1),week(19971231,1);
select week(19950101),week(19950101,1);
select yearweek('1981-12-31',1),yearweek('1982-01-01',1),yearweek('1982-12-31',1),yearweek('1983-01-01',1);
30 31 32 33 34 35 36 37 38
select yearweek('1987-01-01',1),yearweek('1987-01-01');
select week("2000-01-01",0) as '2000', week("2001-01-01",0) as '2001', week("2002-01-01",0) as '2002',week("2003-01-01",0) as '2003', week("2004-01-01",0) as '2004', week("2005-01-01",0) as '2005', week("2006-01-01",0) as '2006';
select week("2000-01-06",0) as '2000', week("2001-01-06",0) as '2001', week("2002-01-06",0) as '2002',week("2003-01-06",0) as '2003', week("2004-01-06",0) as '2004', week("2005-01-06",0) as '2005', week("2006-01-06",0) as '2006';
select week("2000-01-01",1) as '2000', week("2001-01-01",1) as '2001', week("2002-01-01",1) as '2002',week("2003-01-01",1) as '2003', week("2004-01-01",1) as '2004', week("2005-01-01",1) as '2005', week("2006-01-01",1) as '2006';
select week("2000-01-06",1) as '2000', week("2001-01-06",1) as '2001', week("2002-01-06",1) as '2002',week("2003-01-06",1) as '2003', week("2004-01-06",1) as '2004', week("2005-01-06",1) as '2005', week("2006-01-06",1) as '2006';
select yearweek("2000-01-01",0) as '2000', yearweek("2001-01-01",0) as '2001', yearweek("2002-01-01",0) as '2002',yearweek("2003-01-01",0) as '2003', yearweek("2004-01-01",0) as '2004', yearweek("2005-01-01",0) as '2005', yearweek("2006-01-01",0) as '2006';
select yearweek("2000-01-06",0) as '2000', yearweek("2001-01-06",0) as '2001', yearweek("2002-01-06",0) as '2002',yearweek("2003-01-06",0) as '2003', yearweek("2004-01-06",0) as '2004', yearweek("2005-01-06",0) as '2005', yearweek("2006-01-06",0) as '2006';
select yearweek("2000-01-01",1) as '2000', yearweek("2001-01-01",1) as '2001', yearweek("2002-01-01",1) as '2002',yearweek("2003-01-01",1) as '2003', yearweek("2004-01-01",1) as '2004', yearweek("2005-01-01",1) as '2005', yearweek("2006-01-01",1) as '2006';
select yearweek("2000-01-06",1) as '2000', yearweek("2001-01-06",1) as '2001', yearweek("2002-01-06",1) as '2002',yearweek("2003-01-06",1) as '2003', yearweek("2004-01-06",1) as '2004', yearweek("2005-01-06",1) as '2005', yearweek("2006-01-06",1) as '2006';
39 40 41
select week(19981231,2), week(19981231,3), week(20000101,2), week(20000101,3);
select week(20001231,2),week(20001231,3);

42 43 44 45 46 47 48 49 50 51 52 53
select week(19981231,0) as '0', week(19981231,1) as '1', week(19981231,2) as '2', week(19981231,3) as '3', week(19981231,4) as '4', week(19981231,5) as '5', week(19981231,6) as '6', week(19981231,7) as '7';
select week(20000101,0) as '0', week(20000101,1) as '1', week(20000101,2) as '2', week(20000101,3) as '3', week(20000101,4) as '4', week(20000101,5) as '5', week(20000101,6) as '6', week(20000101,7) as '7';
select week(20000106,0) as '0', week(20000106,1) as '1', week(20000106,2) as '2', week(20000106,3) as '3', week(20000106,4) as '4', week(20000106,5) as '5', week(20000106,6) as '6', week(20000106,7) as '7';
select week(20001231,0) as '0', week(20001231,1) as '1', week(20001231,2) as '2', week(20001231,3) as '3', week(20001231,4) as '4', week(20001231,5) as '5', week(20001231,6) as '6', week(20001231,7) as '7';
select week(20010101,0) as '0', week(20010101,1) as '1', week(20010101,2) as '2', week(20010101,3) as '3', week(20010101,4) as '4', week(20010101,5) as '5', week(20010101,6) as '6', week(20010101,7) as '7';

select yearweek(20001231,0), yearweek(20001231,1), yearweek(20001231,2), yearweek(20001231,3), yearweek(20001231,4), yearweek(20001231,5), yearweek(20001231,6), yearweek(20001231,7);

set default_week_format = 6;
select week(20001231), week(20001231,6);
set default_week_format = 0;

unknown's avatar
SCRUM  
unknown committed
54 55 56 57
set default_week_format = 2;
select week(20001231),week(20001231,2),week(20001231,0);
set default_week_format = 0;

58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136
select date_format('1998-12-31','%x-%v'),date_format('1999-01-01','%x-%v');
select date_format('1999-12-31','%x-%v'),date_format('2000-01-01','%x-%v');

select dayname("1962-03-03"),dayname("1962-03-03")+0;
select monthname("1972-03-04"),monthname("1972-03-04")+0;
select time_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T');
select time_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T');
select time_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T');
select time_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T');
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');
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');
select date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND);
select date_add("1997-12-31 23:59:59",INTERVAL 1 MINUTE);
select date_add("1997-12-31 23:59:59",INTERVAL 1 HOUR);
select date_add("1997-12-31 23:59:59",INTERVAL 1 DAY);
select date_add("1997-12-31 23:59:59",INTERVAL 1 MONTH);
select date_add("1997-12-31 23:59:59",INTERVAL 1 YEAR);
select date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND);
select date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE);
select date_add("1997-12-31 23:59:59",INTERVAL "1:1" DAY_HOUR);
select date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH);
select date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND);
select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1" DAY_MINUTE);
select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1:1" DAY_SECOND);

select date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND);
select date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE);
select date_sub("1998-01-01 00:00:00",INTERVAL 1 HOUR);
select date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY);
select date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH);
select date_sub("1998-01-01 00:00:00",INTERVAL 1 YEAR);
select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" MINUTE_SECOND);
select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" HOUR_MINUTE);
select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" DAY_HOUR);
select date_sub("1998-01-01 00:00:00",INTERVAL "1 1" YEAR_MONTH);
select date_sub("1998-01-01 00:00:00",INTERVAL "1:1:1" HOUR_SECOND);
select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1" DAY_MINUTE);
select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND);

select date_add("1997-12-31 23:59:59",INTERVAL 100000 SECOND);
select date_add("1997-12-31 23:59:59",INTERVAL -100000 MINUTE);
select date_add("1997-12-31 23:59:59",INTERVAL 100000 HOUR);
select date_add("1997-12-31 23:59:59",INTERVAL -100000 DAY);
select date_add("1997-12-31 23:59:59",INTERVAL 100000 MONTH);
select date_add("1997-12-31 23:59:59",INTERVAL -100000 YEAR);
select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND);
select date_add("1997-12-31 23:59:59",INTERVAL "-10000:1" HOUR_MINUTE);
select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" DAY_HOUR);
select date_add("1997-12-31 23:59:59",INTERVAL "-100 1" YEAR_MONTH);
select date_add("1997-12-31 23:59:59",INTERVAL "10000:99:99" HOUR_SECOND);
select date_add("1997-12-31 23:59:59",INTERVAL " -10000 99:99" DAY_MINUTE);
select date_add("1997-12-31 23:59:59",INTERVAL "10000 99:99:99" DAY_SECOND);
select "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
select INTERVAL 1 DAY + "1997-12-31";
select "1998-01-01 00:00:00" - INTERVAL 1 SECOND;

select date_sub("1998-01-02",INTERVAL 31 DAY);
select date_add("1997-12-31",INTERVAL 1 SECOND);
select date_add("1997-12-31",INTERVAL 1 DAY);
select date_add(NULL,INTERVAL 100000 SECOND);
select date_add("1997-12-31 23:59:59",INTERVAL NULL SECOND);
select date_add("1997-12-31 23:59:59",INTERVAL NULL MINUTE_SECOND);
select date_add("9999-12-31 23:59:59",INTERVAL 1 SECOND);
select date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND);
select date_add('1998-01-30',Interval 1 month);
select date_add('1998-01-30',Interval '2:1' year_month);
select date_add('1996-02-29',Interval '1' year);
select extract(YEAR FROM "1999-01-02 10:11:12");
select extract(YEAR_MONTH FROM "1999-01-02");
select extract(DAY FROM "1999-01-02");
select extract(DAY_HOUR FROM "1999-01-02 10:11:12");
select extract(DAY_MINUTE FROM "02 10:11:12");
select extract(DAY_SECOND FROM "225 10:11:12");
select extract(HOUR FROM "1999-01-02 10:11:12");
select extract(HOUR_MINUTE FROM "10:11:12");
select extract(HOUR_SECOND FROM "10:11:12");
select extract(MINUTE FROM "10:11:12");
select extract(MINUTE_SECOND FROM "10:11:12");
select extract(SECOND FROM "1999-01-02 10:11:12");
137
select extract(MONTH FROM "2001-02-00");
unknown's avatar
unknown committed
138

139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156
#
# Test big intervals (Bug #3498)
#
SELECT "1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND;
SELECT "1900-01-01 00:00:00" + INTERVAL "1:2147483647" MINUTE_SECOND;
SELECT "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND;SELECT "1900-01-01 00:00:00" + INTERVAL 1<<37 SECOND;
SELECT "1900-01-01 00:00:00" + INTERVAL 1<<31 MINUTE;
SELECT "1900-01-01 00:00:00" + INTERVAL 1<<20 HOUR;

SELECT "1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND;
SELECT "1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE;
SELECT "1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR;
SELECT "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND;

#
# Bug #614 (multiple extracts in where)
#

unknown's avatar
unknown committed
157 158 159
create table t1 (ctime varchar(20));
insert into t1 values ('2001-01-12 12:23:40');
select ctime, hour(ctime) from t1;
unknown's avatar
unknown committed
160
select ctime from t1 where extract(MONTH FROM ctime) = 1 AND extract(YEAR FROM ctime) = 2001;
unknown's avatar
unknown committed
161 162
drop table t1;

163 164 165 166 167 168 169 170 171 172 173 174 175
#
# Test bug with monthname() and NULL
#

create table t1 (id int);
create table t2 (id int, date date);
insert into t1 values (1);
insert into t2 values (1, "0000-00-00");
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;
select monthname(date) from t1 inner join t2 on t1.id = t2.id order by t1.id;
drop table t1,t2;
unknown's avatar
unknown committed
176 177 178 179 180 181 182 183 184

#
# Test bug with month() and year() on text fields with wrong information

CREATE TABLE t1 (updated text) TYPE=MyISAM;
INSERT INTO t1 VALUES ('');
SELECT month(updated) from t1;
SELECT year(updated) from t1;
drop table t1;
unknown's avatar
unknown committed
185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202

#
# Check that functions work identically on 0000-00-00 as a constant and on a
# column
#

create table t1 (d date, dt datetime, t timestamp, c char(10));
insert into t1 values ("0000-00-00", "0000-00-00", "0000-00-00", "0000-00-00");
select dayofyear("0000-00-00"),dayofyear(d),dayofyear(dt),dayofyear(t),dayofyear(c) from t1;
select dayofmonth("0000-00-00"),dayofmonth(d),dayofmonth(dt),dayofmonth(t),dayofmonth(c) from t1;
select month("0000-00-00"),month(d),month(dt),month(t),month(c) from t1;
select quarter("0000-00-00"),quarter(d),quarter(dt),quarter(t),quarter(c) from t1;
select week("0000-00-00"),week(d),week(dt),week(t),week(c) from t1;
select year("0000-00-00"),year(d),year(dt),year(t),year(c) from t1;
select yearweek("0000-00-00"),yearweek(d),yearweek(dt),yearweek(t),yearweek(c) from t1;
select to_days("0000-00-00"),to_days(d),to_days(dt),to_days(t),to_days(c) from t1;
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;
drop table t1;
203

unknown's avatar
unknown committed
204

205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220
#
# Test problem with TIMESTAMP and BETWEEN
#

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(14) NOT NULL, ctime2 timestamp(14) 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");

# The following statement should be fixed to return a row in 4.1
select * from t1, t2 where t1.start between t2.ctime1 and t2.ctime2;
select * from t1, t2 where t1.start >= t2.ctime1 and t1.start <= t2.ctime2;
select * from t1, t3 where t1.start between t3.ctime1 and t3.ctime2;
drop table t1,t2,t3;
221 222 223 224 225 226 227

#
# Test unix timestamp
#
select @a:=FROM_UNIXTIME(1);
select unix_timestamp(@a);
select unix_timestamp('1969-12-01 19:00:01');