date_formats.test 9.37 KB
Newer Older
1 2 3 4
#
# Test of date format functions
#

5
--disable_warnings
6
drop table if exists t1;
7
--enable_warnings
8

9 10 11
SHOW GLOBAL VARIABLES LIKE "%_format%";
SHOW SESSION VARIABLES LIKE "%_format%";

12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
#
# Test setting a lot of different formats to see which formats are accepted and
# which aren't
#

SET time_format='%H%i%s';
SET time_format='%H:%i:%s.%f';
SET time_format='%h-%i-%s.%f%p';
SET time_format='%h:%i:%s.%f %p';
SET time_format='%h:%i:%s%p';

SET date_format='%Y%m%d';
SET date_format='%Y.%m.%d';
SET date_format='%d.%m.%Y';
SET date_format='%m-%d-%Y';

set datetime_format= '%Y%m%d%H%i%s';
set datetime_format= '%Y-%m-%d %H:%i:%s';
set datetime_format= '%m-%d-%y %H:%i:%s.%f';
set datetime_format= '%d-%m-%Y %h:%i:%s%p';
set datetime_format= '%H:%i:%s %Y-%m-%d';
set datetime_format= '%H:%i:%s.%f %m-%d-%Y';
set datetime_format= '%h:%i:%s %p %Y-%m-%d';
set datetime_format= '%h:%i:%s.%f %p %Y-%m-%d';

SHOW SESSION VARIABLES LIKE "%format";

--error 1231
SET time_format='%h:%i:%s';
--error 1231
SET time_format='%H %i:%s';
--error 1231
SET time_format='%H::%i:%s';
--error 1231
SET time_format='%H:%i:%s%f';
--error 1231
SET time_format='%H:%i.%f:%s';
--error 1231
SET time_format='%H:%i:%s%p';
--error 1231
SET time_format='%h:%i:%s.%f %p %Y-%m-%d';
--error 1231
SET time_format='%H%i%s.%f';
--error 1231
SET time_format='%H:%i-%s.%f';
--error 1231
58
SET date_format='%d.%m.%d';
59 60 61 62 63 64 65 66 67 68 69 70 71 72
--error 1231
SET datetime_format='%h.%m.%y %d.%i.%s';
--error 1231
set datetime_format= '%H:%i:%s.%f %p %Y-%m-%d';

#
# Test GLOBAL values

set GLOBAL datetime_format= '%H:%i:%s %Y-%m-%d';
SET SESSION datetime_format=default;
select @@global.datetime_format, @@session.datetime_format;
SET GLOBAL datetime_format=default;
SET SESSION datetime_format=default;
select @@global.datetime_format, @@session.datetime_format;
73 74 75 76 77

SET GLOBAL date_format=default;
SET GLOBAL time_format=default;
SET GLOBAL datetime_format=default;
SET time_format=default;
78
SET date_format=default;
79
SET datetime_format=default;
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

#
# The following tests will work only when we at some point will enable
# dynamic changing of formats
#

# SET date_format='%d.%m.%Y';
# select CAST('01.01.2001' as DATE) as a;
# SET datetime_format='%d.%m.%Y %H.%i.%s';
# select CAST('01.01.2001 05.12.06' as DATETIME) as a;
# SET time_format='%H.%i.%s';
# select CAST('05.12.06' as TIME) as a;
# 
# SET datetime_format='%d.%m.%Y %h:%i:%s %p';
# select CAST('01.01.2001 05:12:06AM' as DATETIME) as a;
# select CAST('01.01.2001 05:12:06 PM' as DATETIME) as a;
# 
# SET time_format='%h:%i:%s %p';
# select CAST('05:12:06 AM' as TIME) as a;
# select CAST('05:12:06.1234PM' as TIME) as a;
# 
# SET time_format='%h.%i.%s %p';
# SET date_format='%d.%m.%y';
# SET datetime_format='%d.%m.%y %h.%i.%s %p';
# select CAST('12-12-06' as DATE) as a;
# 
# select adddate('01.01.97 11.59.59.000001 PM', 10);
# select datediff('31.12.97 11.59:59.000001 PM','01.01.98');
# select weekofyear('31.11.97 11:59:59.000001 PM');
# select makedate(1997,1);
# select addtime('31.12.97 11.59.59.999999 PM', '1 1.1.1.000002');
# select maketime(23,11,12);
# select timediff('01.01.97 11:59:59.000001 PM','31.12.95 11:59:59.000002 PM');
# 
# SET time_format='%i:%s:%H';
# select cast(str_to_date('15-01-2001 12:59:59', '%d-%m-%Y %H:%i:%S') as TIME);

#
# Test of str_to_date
#

121 122
# PS doesn't support fraction of a seconds
--disable_ps_protocol
123 124
select str_to_date(concat('15-01-2001',' 2:59:58.999'),
	           concat('%d-%m-%Y',' ','%H:%i:%s.%f'));
125
--enable_ps_protocol
126 127 128 129

create table t1 (date char(30), format char(30) not null);
insert into t1 values
('2003-01-02 10:11:12', '%Y-%m-%d %H:%i:%S'),
130
('03-01-02 8:11:2.123456', '%y-%m-%d %H:%i:%S.%#'),
131 132 133 134 135 136 137
('2003-01-02 10:11:12 PM', '%Y-%m-%d %h:%i:%S %p'),
('2003-01-02 01:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f%p'),
('2003-01-02 02:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f %p'),
('2003-01-02 12:11:12.12345 am', '%Y-%m-%d %h:%i:%S.%f%p'),
('2003-01-02 11:11:12Pm', '%Y-%m-%d %h:%i:%S%p'),
('10:20:10', '%H:%i:%s'),
('10:20:10', '%h:%i:%s.%f'),
138
('10:20:10', '%T'),
139
('10:20:10AM', '%h:%i:%s%p'),
140
('10:20:10AM', '%r'),
141 142 143 144 145
('10:20:10.44AM', '%h:%i:%s.%f%p'),
('15-01-2001 12:59:58', '%d-%m-%Y %H:%i:%S'),
('15 September 2001', '%d %M %Y'),
('15 SEPTEMB 2001', '%d %M %Y'),
('15 MAY 2001', '%d %b %Y'),
146
('15th May 2001', '%D %b %Y'),
147 148 149 150
('Sunday 15 MAY 2001', '%W %d %b %Y'),
('Sund 15 MAY 2001', '%W %d %b %Y'),
('Tuesday 00 2002', '%W %U %Y'),
('Thursday 53 1998', '%W %u %Y'),
151 152 153 154
('Sunday 01 2001', '%W %v %x'),
('Tuesday 52 2001', '%W %V %X'),
('060 2004', '%j %Y'),
('4 53 1998', '%w %u %Y'),
155 156 157 158
('15-01-2001', '%d-%m-%Y %H:%i:%S'),
('15-01-20', '%d-%m-%y'),
('15-2001-1', '%d-%Y-%c');

159 160
# PS doesn't support fractional seconds
--disable_ps_protocol
161 162 163 164 165 166 167 168
select date,format,str_to_date(date, format) as str_to_date from t1;
# Use as a string
select date,format,concat('',str_to_date(date, format)) as con from t1;
# Use as datetime
select date,format,cast(str_to_date(date, format) as datetime) as datetime from t1;
select date,format,DATE(str_to_date(date, format)) as date2 from t1;
select date,format,TIME(str_to_date(date, format)) as time from t1;
select date,format,concat(TIME(str_to_date(date, format))) as time2 from t1;
169 170
# Test small bug in %f handling
select concat('',str_to_date('8:11:2.123456 03-01-02','%H:%i:%S.%f %y-%m-%d'));
171

172
# Test wrong dates or converion specifiers
173 174 175 176 177 178 179 180 181 182 183 184 185

truncate table t1;
insert into t1 values
('2003-01-02 10:11:12 PM', '%Y-%m-%d %H:%i:%S %p'),
('2003-01-02 10:11:12.123456', '%Y-%m-%d %h:%i:%S %p'),
('2003-01-02 10:11:12AM', '%Y-%m-%d %h:%i:%S.%f %p'),
('2003-01-02 10:11:12AN', '%Y-%m-%d %h:%i:%S%p'),
('2003-01-02 10:11:12 PM', '%y-%m-%d %H:%i:%S %p'),
('10:20:10AM', '%H:%i:%s%p'),
('15 Septembei 2001', '%d %M %Y'),
('15 Ju 2001', '%d %M %Y'),
('Sund 15 MA', '%W %d %b %Y'),
('Thursdai 12 1998', '%W %u %Y'),
186 187 188 189 190 191
('Sunday 01 2001', '%W %v %X'),
('Tuesday 52 2001', '%W %V %x'),
('Tuesday 52 2001', '%W %V %Y'),
('Tuesday 52 2001', '%W %u %x'),
('7 53 1998', '%w %u %Y'),
(NULL, get_format(DATE,'USA'));
192 193 194 195 196 197 198 199 200 201 202 203 204 205 206
select date,format,str_to_date(date, format) as str_to_date from t1;
select date,format,concat(str_to_date(date, format),'') as con from t1;

# Test 'maybe' date formats and 'strange but correct' results

truncate table t1;
insert into t1 values
('10:20:10AM', '%h:%i:%s'),
('2003-01-02 10:11:12', '%Y-%m-%d %h:%i:%S'),
('03-01-02 10:11:12 PM', '%Y-%m-%d %h:%i:%S %p');

select date,format,str_to_date(date, format) as str_to_date from t1;
select date,format,concat(str_to_date(date, format),'') as con from t1;

drop table t1;
207
--enable_ps_protocol
208 209 210 211

#
# Test of get_format
#
212 213 214 215

select get_format(DATE, 'USA') as a;
select get_format(TIME, 'internal') as a;
select get_format(DATETIME, 'eur') as a;
216
select get_format(TIMESTAMP, 'eur') as a;
217 218
select get_format(DATE, 'TEST') as a;
select str_to_date('15-01-2001 12:59:59', GET_FORMAT(DATE,'USA'));
219

220
explain extended select makedate(1997,1), addtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002"),subtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002"),timediff("01.01.97 11:59:59.000001 PM","31.12.95 11:59:59.000002 PM"),cast(str_to_date("15-01-2001 12:59:59", "%d-%m-%Y %H:%i:%S") as TIME), maketime(23,11,12),microsecond("1997-12-31 23:59:59.000001");
221 222 223 224 225 226 227 228 229

#
# Test of date_format()
#

create table t1 (d date);
insert into t1 values ('2004-07-14'),('2005-07-14');
select date_format(d,"%d") from t1 order by 1;
drop table t1;
230

231 232
# PS doesn't support fractional seconds
--disable_ps_protocol
233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260
select str_to_date("2003-....01ABCD-02 10:11:12.0012", "%Y-%.%m%@-%d %H:%i:%S.%f") as a;


create table t1 select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f") as f1,
			str_to_date("10:11:12.0012", "%H:%i:%S.%f") as f2,
			str_to_date("2003-01-02", "%Y-%m-%d") as f3,
			str_to_date("02", "%d") as f4, str_to_date("02 10", "%d %H") as f5;
describe t1;
select * from t1;
drop table t1;

create table t1 select "02 10" as a, "%d %H" as b;
select str_to_date(a,b) from t1;
create table t2 select str_to_date(a,b) from t1;
describe t2;
select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f") as f1,
       str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S") as f2,
       str_to_date("2003-01-02", "%Y-%m-%d") as f3,
       str_to_date("02 10:11:12", "%d %H:%i:%S.%f") as f4,
       str_to_date("02 10:11:12", "%d %H:%i:%S") as f5,
       str_to_date("02 10", "%d %f") as f6;
drop table t1, t2;
select str_to_date("2003-01-02 10:11:12.0012ABCD", "%Y-%m-%d %H:%i:%S.%f") as f1,
       addtime("-01:01:01.01 GGG", "-23:59:59.1") as f2,
       microsecond("1997-12-31 23:59:59.01XXXX") as f3;

select str_to_date("2003-04-05  g", "%Y-%m-%d") as f1,
       str_to_date("2003-04-05 10:11:12.101010234567", "%Y-%m-%d %H:%i:%S.%f") as f2;
261
--enable_ps_protocol
262

263 264 265 266 267 268 269 270
#
# Bug #14016 
#
create table t1 (f1 datetime);
insert into t1 (f1) values ("2005-01-01");
insert into t1 (f1) values ("2005-02-01");
select date_format(f1, "%m") as d1, date_format(f1, "%M") as d2 from t1 order by date_format(f1, "%M");
drop table t1;
gvb@phoenix.(none)'s avatar
gvb@phoenix.(none) committed
271 272 273 274 275 276

#
# Bug #15828
#
select str_to_date( 1, NULL );
select str_to_date( NULL, 1 );
gvb@phoenix.(none)'s avatar
gvb@phoenix.(none) committed
277
select str_to_date( 1, IF(1=1,NULL,NULL) );
278
# End of 4.1 tests
279 280 281 282 283 284 285 286 287 288 289

#
# Bug#11326
#

SELECT TIME_FORMAT("24:00:00", '%r');
SELECT TIME_FORMAT("00:00:00", '%r');
SELECT TIME_FORMAT("12:00:00", '%r');
SELECT TIME_FORMAT("15:00:00", '%r');
SELECT TIME_FORMAT("01:00:00", '%r');
SELECT TIME_FORMAT("25:00:00", '%r');
290 291 292 293 294 295 296 297 298 299 300

#
# Bug#11324
#

SELECT TIME_FORMAT("00:00:00", '%l %p');
SELECT TIME_FORMAT("01:00:00", '%l %p');
SELECT TIME_FORMAT("12:00:00", '%l %p');
SELECT TIME_FORMAT("23:00:00", '%l %p');
SELECT TIME_FORMAT("24:00:00", '%l %p');
SELECT TIME_FORMAT("25:00:00", '%l %p');