1
drop table if exists t1,t2;
2
create table t1 (a char(16) primary key, b date, c datetime);
3
insert into t1 SET a='test 2000-01-01', b='2000-01-01', c='2000-01-01';
4
select * from t1 where c = '2000-01-01';
6
test 2000-01-01 2000-01-01 2000-01-01 00:00:00
7
select * from t1 where b = '2000-01-01';
9
test 2000-01-01 2000-01-01 2000-01-01 00:00:00
11
CREATE TABLE t1 (name char(6),cdate date, primary key (name,cdate));
12
INSERT INTO t1 VALUES ('name1','1998-01-01');
13
INSERT INTO t1 VALUES ('name2','1998-01-01');
14
INSERT INTO t1 VALUES ('name1','1998-01-02');
15
INSERT INTO t1 VALUES ('name2','1998-01-02');
16
CREATE TABLE t2 (cdate date, note char(6), primary key (cdate,note));
17
INSERT INTO t2 VALUES ('1998-01-01','note01');
18
INSERT INTO t2 VALUES ('1998-01-02','note02');
19
select name,t1.cdate,note from t1,t2 where t1.cdate=t2.cdate and t1.cdate='1998-01-01';
21
name1 1998-01-01 note01
22
name2 1998-01-01 note01
24
CREATE TABLE t1 ( datum DATE primary key);
25
INSERT INTO t1 VALUES ( "2000-1-1" );
26
INSERT INTO t1 VALUES ( "2000-1-2" );
27
INSERT INTO t1 VALUES ( "2000-1-3" );
28
INSERT INTO t1 VALUES ( "2000-1-4" );
29
INSERT INTO t1 VALUES ( "2000-1-5" );
30
SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND cast("2000-1-4" as date);
35
SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND datum - INTERVAL 100 DAY;
42
primary key (user_id, summa, rdate)
44
INSERT INTO t1 VALUES ('aaa',100,'1998-01-01');
45
INSERT INTO t1 VALUES ('aaa',200,'1998-01-03');
46
INSERT INTO t1 VALUES ('bbb',50,'1998-01-02');
47
INSERT INTO t1 VALUES ('bbb',200,'1998-01-04');
48
select max(rdate) as s from t1 where rdate < '1998-01-03' having s> "1998-01-01";
51
select max(rdate) as s from t1 having s="1998-01-04";
54
select max(rdate+0) as s from t1 having s="19980104";
58
create table t1 (date date primary key);
59
insert into t1 values ("2000-08-10"),("2000-08-11");
60
select date_add(date,INTERVAL 1 DAY),date_add(date,INTERVAL 1 SECOND) from t1;
61
date_add(date,INTERVAL 1 DAY) date_add(date,INTERVAL 1 SECOND)
62
2000-08-11 2000-08-10 00:00:01
63
2000-08-12 2000-08-11 00:00:01
65
CREATE TABLE t1(AFIELD INT primary key);
66
INSERT INTO t1 VALUES(1);
67
CREATE TABLE t2(GMT VARCHAR(32) primary key);
68
INSERT INTO t2 VALUES('GMT-0800');
69
SELECT DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ' , t2.GMT)) FROM t1 CROSS JOIN t2 GROUP BY t1.AFIELD;
70
DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ' , t2.GMT))
71
Wed, 06 March 2002 10:11:12 GMT-0800
72
INSERT INTO t1 VALUES(2);
73
SELECT DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ' , t2.GMT)), DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ' , t2.GMT)) FROM t1 CROSS JOIN t2 GROUP BY t1.AFIELD;
74
DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ' , t2.GMT)) DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ' , t2.GMT))
75
Wed, 06 March 2002 10:11:12 GMT-0800 Wed, 06 March 2002 10:11:12 GMT-0800
76
Wed, 06 March 2002 10:11:12 GMT-0800 Wed, 06 March 2002 10:11:12 GMT-0800
78
CREATE TABLE t1 (f1 DATE, pk int auto_increment primary key);
79
CREATE TABLE t2 (f2 VARCHAR(8), pk int auto_increment primary key);
80
CREATE TABLE t3 (f2 CHAR(8), pk int auto_increment primary key);
81
INSERT INTO t1 (f1) VALUES ('1978-11-26');
82
INSERT INTO t2 (f2) SELECT f1+0 FROM t1;
83
INSERT INTO t2 (f2) SELECT f1+0 FROM t1 UNION SELECT f1+0 FROM t1;
84
INSERT INTO t3 (f2) SELECT f1+0 FROM t1;
85
INSERT INTO t3 (f2) SELECT f1+0 FROM t1 UNION SELECT f1+0 FROM t1;
94
DROP TABLE t1, t2, t3;
95
create table t1(start_date date, end_date date, primary key (start_date, end_date));
96
insert into t1 values ('2000-01-01','2000-01-02');
97
select 1 from t1 where cast('2000-01-01 12:01:01' as datetime) between start_date and end_date;
104
select year(@d), month(@d), day(@d), cast(@d as date);
105
year(@d) month(@d) day(@d) cast(@d as date)
106
2000 11 11 2000-11-11
110
select year(@d), month(@d), day(@d), cast(@d as date);
111
year(@d) month(@d) day(@d) cast(@d as date)
112
2001 11 11 2001-11-11
116
select year(@d), month(@d), day(@d), cast(@d as date);
117
ERROR HY000: Received an invalid datetime value '1311'.
118
create table t1 (d date , dt datetime , ts timestamp, primary key (d));
119
insert into t1 values (9912101,9912101,9912101);
120
ERROR HY000: Received an invalid DATE value '9912101'.
121
insert into t1 values (11111,11111,11111);
124
2001-11-11 2001-11-11 00:00:00 2001-11-11 00:00:00
126
CREATE TABLE t1 (a DATE, b int, PRIMARY KEY (a,b));
127
INSERT INTO t1 VALUES (DATE(NOW()), 1);
128
SELECT COUNT(*) FROM t1 WHERE a = NOW();
131
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW();
132
id select_type table type possible_keys key key_len ref rows Extra
133
1 SIMPLE NULL NULL NULL NULL NULL NULL # Impossible WHERE noticed after reading const tables
134
INSERT INTO t1 VALUES (DATE(NOW()), 2);
135
SELECT COUNT(*) FROM t1 WHERE a = NOW();
138
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW();
139
id select_type table type possible_keys key key_len ref rows Extra
140
1 SIMPLE NULL NULL NULL NULL NULL NULL # Impossible WHERE noticed after reading const tables
141
SELECT COUNT(*) FROM t1 WHERE a = NOW() AND b = 1;
144
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW() AND b = 1;
145
id select_type table type possible_keys key key_len ref rows Extra
146
1 SIMPLE NULL NULL NULL NULL NULL NULL # Impossible WHERE noticed after reading const tables
148
CREATE TABLE t1 (f1 date primary key) SELECT curdate() AS f1;
149
SELECT hour(f1), minute(f1), second(f1) FROM t1;
150
hour(f1) minute(f1) second(f1)