« back to all changes in this revision
Viewing changes to tests/r/func_time.result
- browse files at revision 830
- compare with another revision
- compare with revision 982.1.2
- download diff
- download tarball
- view history from revision 830
- Committer: Brian Aker
- Date: 2009-02-04 18:28:00 UTC
- mfrom: (813.1.26 new-temporal)
- Revision ID: brian@tangent.org-20090204182800-ra3p8rlu4zrs9fvn
Merge from Monty.
- files added:
- tests/r/hour.result
- files removed:
- drizzled/function/time/week.cc
- files modified:
- drizzled/Makefile.am
added
removed
tests/r/func_time.result
44
44
1 1998 365
45
45
select month("2001-02-00"),year("2001-01-01");
46
46
ERROR HY000: Received an invalid datetime value '2001-02-00'.
47
select DAYOFYEAR("1997-03-03"), WEEK("1998-03-03"), QUARTER(980303);
48
DAYOFYEAR("1997-03-03") WEEK("1998-03-03") QUARTER(980303)
49
62 9 1
47
select DAYOFYEAR("1997-03-03"), QUARTER(980303);
48
DAYOFYEAR("1997-03-03") QUARTER(980303)
49
62 1
50
50
select HOUR("1997-03-03 23:03:22"), MINUTE("23:03:22"), SECOND(230322);
51
51
HOUR("1997-03-03 23:03:22") MINUTE("23:03:22") SECOND(230322)
52
52
23 3 22
53
select week(19980101),week(19970101),week(19980101,1),week(19970101,1);
54
week(19980101) week(19970101) week(19980101,1) week(19970101,1)
55
0 0 1 1
56
select week(19981231),week(19971231),week(19981231,1),week(19971231,1);
57
week(19981231) week(19971231) week(19981231,1) week(19971231,1)
58
52 52 53 53
59
select week(19950101),week(19950101,1);
60
week(19950101) week(19950101,1)
61
1 0
62
select yearweek('1981-12-31',1),yearweek('1982-01-01',1),yearweek('1982-12-31',1),yearweek('1983-01-01',1);
63
yearweek('1981-12-31',1) yearweek('1982-01-01',1) yearweek('1982-12-31',1) yearweek('1983-01-01',1)
64
198153 198153 198252 198252
65
select yearweek('1987-01-01',1),yearweek('1987-01-01');
66
yearweek('1987-01-01',1) yearweek('1987-01-01')
67
198701 198652
68
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';
69
2000 2001 2002 2003 2004 2005 2006
70
0 0 0 0 0 0 1
71
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';
72
2000 2001 2002 2003 2004 2005 2006
73
1 0 1 1 1 1 1
74
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';
75
2000 2001 2002 2003 2004 2005 2006
76
0 1 1 1 1 0 0
77
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';
78
2000 2001 2002 2003 2004 2005 2006
79
1 1 1 2 2 1 1
80
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';
81
2000 2001 2002 2003 2004 2005 2006
82
199952 200053 200152 200252 200352 200452 200601
83
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';
84
2000 2001 2002 2003 2004 2005 2006
85
200001 200053 200201 200301 200401 200501 200601
86
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';
87
2000 2001 2002 2003 2004 2005 2006
88
199952 200101 200201 200301 200401 200453 200552
89
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';
90
2000 2001 2002 2003 2004 2005 2006
91
200001 200101 200201 200302 200402 200501 200601
92
select week(19981231,2), week(19981231,3), week(20000101,2), week(20000101,3);
93
week(19981231,2) week(19981231,3) week(20000101,2) week(20000101,3)
94
52 53 52 52
95
select week(20001231,2),week(20001231,3);
96
week(20001231,2) week(20001231,3)
97
53 52
98
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';
99
0 1 2 3 4 5 6 7
100
52 53 52 53 52 52 52 52
101
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';
102
0 1 2 3 4 5 6 7
103
0 0 52 52 0 0 52 52
104
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';
105
0 1 2 3 4 5 6 7
106
1 1 1 1 1 1 1 1
107
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';
108
0 1 2 3 4 5 6 7
109
53 52 53 52 53 52 1 52
110
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';
111
0 1 2 3 4 5 6 7
112
0 1 53 1 1 1 1 1
113
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);
114
yearweek(20001231,0) yearweek(20001231,1) yearweek(20001231,2) yearweek(20001231,3) yearweek(20001231,4) yearweek(20001231,5) yearweek(20001231,6) yearweek(20001231,7)
115
200053 200052 200053 200052 200101 200052 200101 200052
116
set default_week_format = 6;
117
select week(20001231), week(20001231,6);
118
week(20001231) week(20001231,6)
119
1 1
120
set default_week_format = 0;
121
set default_week_format = 2;
122
select week(20001231),week(20001231,2),week(20001231,0);
123
week(20001231) week(20001231,2) week(20001231,0)
124
53 53 53
125
set default_week_format = 0;
126
53
select date_format('1998-12-31','%x-%v'),date_format('1999-01-01','%x-%v');
127
54
date_format('1998-12-31','%x-%v') date_format('1999-01-01','%x-%v')
128
55
1998-53 1998-53
333
260
extract(DAY_HOUR FROM "1999-01-02 10:11:12")
334
261
210
335
262
select extract(DAY_MINUTE FROM "02 10:11:12");
336
extract(DAY_MINUTE FROM "02 10:11:12")
337
21011
263
ERROR HY000: Received an invalid datetime value '02 10:11:12'.
338
264
select extract(DAY_SECOND FROM "225 10:11:12");
339
extract(DAY_SECOND FROM "225 10:11:12")
340
8385959
341
Warnings:
342
Warning 1292 Truncated incorrect time value: '225 10:11:12'
265
ERROR HY000: Received an invalid datetime value '225 10:11:12'.
343
266
select extract(HOUR FROM "1999-01-02 10:11:12");
344
267
extract(HOUR FROM "1999-01-02 10:11:12")
345
268
10
359
282
extract(SECOND FROM "1999-01-02 10:11:12")
360
283
12
361
284
select extract(MONTH FROM "2001-02-00");
362
extract(MONTH FROM "2001-02-00")
363
2
285
ERROR HY000: Received an invalid datetime value '2001-02-00'.
364
286
SELECT EXTRACT(QUARTER FROM '2004-01-15') AS quarter;
365
287
quarter
366
288
1
457
379
select month("0000-00-00"),month(d),month(dt),month(t),month(c) from t1;
458
380
ERROR HY000: Received an invalid datetime value '0000-00-00'.
459
381
select quarter("0000-00-00"),quarter(d),quarter(dt),quarter(t),quarter(c) from t1;
460
quarter("0000-00-00") quarter(d) quarter(dt) quarter(t) quarter(c)
461
0 0 0 0 0
462
select week("0000-00-00"),week(d),week(dt),week(t),week(c) from t1;
463
week("0000-00-00") week(d) week(dt) week(t) week(c)
464
NULL NULL NULL NULL NULL
465
Warnings:
466
Warning 1292 Incorrect datetime value: '0000-00-00'
467
Warning 1292 Incorrect datetime value: '0000-00-00'
382
ERROR HY000: Received an invalid datetime value '0000-00-00'.
468
383
select year("0000-00-00"),year(d),year(dt),year(t),year(c) from t1;
469
384
ERROR HY000: Received an invalid datetime value '0000-00-00'.
470
select yearweek("0000-00-00"),yearweek(d),yearweek(dt),yearweek(t),yearweek(c) from t1;
471
yearweek("0000-00-00") yearweek(d) yearweek(dt) yearweek(t) yearweek(c)
472
NULL NULL NULL NULL NULL
473
Warnings:
474
Warning 1292 Incorrect datetime value: '0000-00-00'
475
Warning 1292 Incorrect datetime value: '0000-00-00'
476
385
select to_days("0000-00-00"),to_days(d),to_days(dt),to_days(t),to_days(c) from t1;
477
386
to_days("0000-00-00") to_days(d) to_days(dt) to_days(t) to_days(c)
478
387
NULL NULL NULL NULL NULL
480
389
Warning 1292 Incorrect datetime value: '0000-00-00'
481
390
Warning 1292 Incorrect datetime value: '0000-00-00'
482
391
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;
483
extract(MONTH FROM "0000-00-00") extract(MONTH FROM d) extract(MONTH FROM dt) extract(MONTH FROM t) extract(MONTH FROM c)
484
0 0 0 0 0
392
ERROR HY000: Received an invalid datetime value '0000-00-00'.
485
393
drop table t1;
486
394
CREATE TABLE t1 ( start datetime default NULL);
487
395
INSERT INTO t1 VALUES ('2002-10-21 00:00:00'),('2002-10-28 00:00:00'),('2002-11-04 00:00:00');
796
704
select strcmp(concat(utc_date(),' ',utc_time()),utc_timestamp())=0;
797
705
strcmp(concat(utc_date(),' ',utc_time()),utc_timestamp())=0
798
706
1
799
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),WEEK("1998-03-03"),yearweek("2000-01-01",1),week(19950101,1),year("98-02-03"),weekday(curdate())-weekday(now()),dayname("1962-03-03"),unix_timestamp(),sec_to_time(time_to_sec("0:30:47")/6.21),curtime(),utc_time(),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);
707
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(),sec_to_time(time_to_sec("0:30:47")/6.21),curtime(),utc_time(),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);
800
708
id select_type table type possible_keys key key_len ref rows filtered Extra
801
709
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
802
710
Warnings:
803
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)`,week('1998-03-03',0) AS `WEEK("1998-03-03")`,yearweek('2000-01-01',1) AS `yearweek("2000-01-01",1)`,week(19950101,1) AS `week(19950101,1)`,year('98-02-03') AS `year("98-02-03")`,(weekday(curdate()) - weekday(now())) AS `weekday(curdate())-weekday(now())`,dayname('1962-03-03') AS `dayname("1962-03-03")`,unix_timestamp() AS `unix_timestamp()`,sec_to_time((time_to_sec('0:30:47') / 6.21)) AS `sec_to_time(time_to_sec("0:30:47")/6.21)`,curtime() AS `curtime()`,utc_time() AS `utc_time()`,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)`
711
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()`,sec_to_time((time_to_sec('0:30:47') / 6.21)) AS `sec_to_time(time_to_sec("0:30:47")/6.21)`,curtime() AS `curtime()`,utc_time() AS `utc_time()`,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)`
804
712
SET @TMP='2007-08-01 12:22:49';
805
713
CREATE TABLE t1 (d DATETIME);
806
714
INSERT INTO t1 VALUES ('2007-08-01 12:22:59');
945
853
MAKETIME(0, 0, 4294967296)
946
854
NULL
947
855
SELECT EXTRACT(HOUR FROM '100000:02:03');
948
EXTRACT(HOUR FROM '100000:02:03')
949
838
950
Warnings:
951
Warning 1292 Truncated incorrect time value: '100000:02:03'
856
ERROR HY000: Received an invalid datetime value '100000:02:03'.
952
857
CREATE TABLE t1(f1 TIME);
953
858
INSERT INTO t1 VALUES('916:00:00 a');
954
859
ERROR 22007: Incorrect time value: '916:00:00 a' for column 'f1' at row 1
995
900
select last_day('0000-00-00');
996
901
last_day('0000-00-00')
997
902
NULL
998
select isnull(week(now() + 0)), isnull(week(now() + 0.2)),
999
week(20061108), week(20061108.01), week(20061108085411.000002);
1000
isnull(week(now() + 0)) isnull(week(now() + 0.2)) week(20061108) week(20061108.01) week(20061108085411.000002)
1001
0 0 45 45 45
1002
903
End of 4.1 tests
1003
904
explain extended select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a1,
1004
905
timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12:58:58.119999') as a2;
1112
1013
FROM t1;
1113
1014
t1 t2 SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ) QUARTER(d)
1114
1015
10:00:00 NULL NULL NULL
1115
11:00:00 11:15:00 00:15:00 1
1016
11:00:00 11:15:00 00:15:00 NULL
1116
1017
SELECT t1, t2, SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ), QUARTER(d)
1117
1018
FROM t1 ORDER BY a DESC;
1118
1019
t1 t2 SEC_TO_TIME( TIME_TO_SEC( t2 ) - TIME_TO_SEC( t1 ) ) QUARTER(d)
Loggerhead is a web-based interface for Breezy
Version: 2.0.1