summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/func_sapdb.result194
-rw-r--r--mysql-test/t/func_sapdb.test97
-rw-r--r--sql/field.cc2
-rw-r--r--sql/item_create.cc35
-rw-r--r--sql/item_create.h7
-rw-r--r--sql/item_timefunc.cc534
-rw-r--r--sql/item_timefunc.h161
-rw-r--r--sql/lex.h16
-rw-r--r--sql/mysql_priv.h1
-rw-r--r--sql/protocol.cc4
-rw-r--r--sql/sql_yacc.yy33
-rw-r--r--sql/time.cc13
12 files changed, 1065 insertions, 32 deletions
diff --git a/mysql-test/r/func_sapdb.result b/mysql-test/r/func_sapdb.result
new file mode 100644
index 00000000000..5c044d00726
--- /dev/null
+++ b/mysql-test/r/func_sapdb.result
@@ -0,0 +1,194 @@
+drop table if exists t1, test;
+select extract(DAY_MICROSECOND FROM "1999-01-02 10:11:12.000123");
+extract(DAY_MICROSECOND FROM "1999-01-02 10:11:12.000123")
+2101112000123
+select extract(HOUR_MICROSECOND FROM "1999-01-02 10:11:12.000123");
+extract(HOUR_MICROSECOND FROM "1999-01-02 10:11:12.000123")
+101112000123
+select extract(MINUTE_MICROSECOND FROM "1999-01-02 10:11:12.000123");
+extract(MINUTE_MICROSECOND FROM "1999-01-02 10:11:12.000123")
+1112000123
+select extract(SECOND_MICROSECOND FROM "1999-01-02 10:11:12.000123");
+extract(SECOND_MICROSECOND FROM "1999-01-02 10:11:12.000123")
+12000123
+select extract(MICROSECOND FROM "1999-01-02 10:11:12.000123");
+extract(MICROSECOND FROM "1999-01-02 10:11:12.000123")
+123
+select date_format("1997-12-31 23:59:59.000002", "%f");
+date_format("1997-12-31 23:59:59.000002", "%f")
+000002
+select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000 99:99:99.999999" DAY_MICROSECOND);
+date_add("1997-12-31 23:59:59.000002",INTERVAL "10000 99:99:99.999999" DAY_MICROSECOND)
+2025-05-23 04:40:39.000001
+select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99:99.999999" HOUR_MICROSECOND);
+date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99:99.999999" HOUR_MICROSECOND)
+1999-02-21 17:40:39.000001
+select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99.999999" MINUTE_MICROSECOND);
+date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99.999999" MINUTE_MICROSECOND)
+1998-01-07 22:41:39.000001
+select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000.999999" SECOND_MICROSECOND);
+date_add("1997-12-31 23:59:59.000002",INTERVAL "10000.999999" SECOND_MICROSECOND)
+1998-01-01 02:46:40.000001
+select date_add("1997-12-31 23:59:59.000002",INTERVAL "999999" MICROSECOND);
+date_add("1997-12-31 23:59:59.000002",INTERVAL "999999" MICROSECOND)
+1998-01-01 00:00:00.000001
+select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND);
+date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND)
+1997-12-30 22:58:58.999999
+select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND);
+date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND)
+1997-12-31 22:58:58.999999
+select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND);
+date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND)
+1997-12-31 23:58:58.999999
+select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND);
+date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND)
+1997-12-31 23:59:58.999999
+select date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND);
+date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND)
+1997-12-31 23:59:59.999999
+select adddate("1997-12-31 23:59:59.000001", 10);
+adddate("1997-12-31 23:59:59.000001", 10)
+1998-01-10 23:59:59.000001
+select subdate("1997-12-31 23:59:59.000001", 10);
+subdate("1997-12-31 23:59:59.000001", 10)
+1997-12-21 23:59:59.000001
+select datediff("1997-12-31 23:59:59.000001","1997-12-30");
+datediff("1997-12-31 23:59:59.000001","1997-12-30")
+1
+select datediff("1997-11-31 23:59:59.000001","1997-12-31");
+datediff("1997-11-31 23:59:59.000001","1997-12-31")
+-30
+select datediff("1997-11-31 23:59:59.000001",null);
+datediff("1997-11-31 23:59:59.000001",null)
+NULL
+select weekofyear("1997-11-31 23:59:59.000001");
+weekofyear("1997-11-31 23:59:59.000001")
+49
+select makedate(1997,1);
+makedate(1997,1)
+1997-01-01
+select makedate(1997,0);
+makedate(1997,0)
+NULL
+select addtime("1997-12-31 23:59:59.999999", "1 1:1:1.000002");
+addtime("1997-12-31 23:59:59.999999", "1 1:1:1.000002")
+1998-01-02 01:01:01.000001
+select subtime("1997-12-31 23:59:59.000001", "1 1:1:1.000002");
+subtime("1997-12-31 23:59:59.000001", "1 1:1:1.000002")
+1997-12-30 22:58:57.999999
+select addtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999");
+addtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999")
+NULL
+select subtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999");
+subtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999")
+NULL
+select subtime("01:00:00.999999", "02:00:00.999998");
+subtime("01:00:00.999999", "02:00:00.999998")
+-00:59:59.999999
+select subtime("02:01:01.999999", "01:01:01.999999");
+subtime("02:01:01.999999", "01:01:01.999999")
+01:00:00.000000
+select timediff("1997-01-01 23:59:59.000001","1995-12-31 23:59:59.000002");
+timediff("1997-01-01 23:59:59.000001","1995-12-31 23:59:59.000002")
+8807:59:59.999999
+select timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002");
+timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002")
+46:58:57.999999
+select timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002");
+timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002")
+-23:59:59.999999
+select timediff("1997-12-31 23:59:59.000001","23:59:59.000001");
+timediff("1997-12-31 23:59:59.000001","23:59:59.000001")
+NULL
+select timediff("2000:01:01 00:00:00", "2000:01:01 00:00:00.1");
+timediff("2000:01:01 00:00:00", "2000:01:01 00:00:00.1")
+-00:00:00.000001
+select maketime(10,11,12);
+maketime(10,11,12)
+10:11:12
+select maketime(25,11,12);
+maketime(25,11,12)
+25:11:12
+select maketime(-25,11,12);
+maketime(-25,11,12)
+-25:11:12
+select timestamp("2001-12-01", "01:01:01.999999");
+timestamp("2001-12-01", "01:01:01.999999")
+2001-12-01 01:01:01.999999
+select timestamp("2001-13-01", "01:01:01.000001");
+timestamp("2001-13-01", "01:01:01.000001")
+NULL
+select timestamp("2001-12-01", "25:01:01");
+timestamp("2001-12-01", "25:01:01")
+2001-12-02 01:01:01
+select day("1997-12-31 23:59:59.000001");
+day("1997-12-31 23:59:59.000001")
+31
+select date("1997-12-31 23:59:59.000001");
+date("1997-12-31 23:59:59.000001")
+1997-12-31
+select date("1997-13-31 23:59:59.000001");
+date("1997-13-31 23:59:59.000001")
+NULL
+select time("1997-12-31 23:59:59.000001");
+time("1997-12-31 23:59:59.000001")
+23:59:59.000001
+select time("1997-12-31 25:59:59.000001");
+time("1997-12-31 25:59:59.000001")
+NULL
+select microsecond("1997-12-31 23:59:59.000001");
+microsecond("1997-12-31 23:59:59.000001")
+1
+create table t1
+select makedate(1997,1) as f1,
+addtime(cast("1997-12-31 23:59:59.000001" as datetime), "1 1:1:1.000002") as f2,
+addtime(cast("23:59:59.999999" as time) , "1 1:1:1.000002") as f3,
+timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002") as f4,
+timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002") as f5,
+maketime(10,11,12) as f6,
+timestamp("2001-12-01", "01:01:01") as f7,
+date("1997-12-31 23:59:59.000001") as f8,
+time("1997-12-31 23:59:59.000001") as f9;
+describe t1;
+Field Type Null Key Default Extra
+f1 date 0000-00-00
+f2 datetime 0000-00-00 00:00:00
+f3 time 00:00:00
+f4 time 00:00:00
+f5 time 00:00:00
+f6 time 00:00:00
+f7 datetime 0000-00-00 00:00:00
+f8 date 0000-00-00
+f9 time 00:00:00
+select * from t1;
+f1 f2 f3 f4 f5 f6 f7 f8 f9
+1997-01-01 1998-01-02 01:01:00 49:01:01 46:58:57 -23:59:59 10:11:12 2001-12-01 01:01:01 1997-12-31 23:59:59
+create table test(t1 datetime, t2 time, t3 time, t4 datetime);
+insert into test values
+('2001-01-01 01:01:01', '01:01:01', null, '2001-02-01 01:01:01'),
+('2001-01-01 01:01:01', '-01:01:01', '-23:59:59', "1997-12-31 23:59:59.000001"),
+('1997-12-31 23:59:59.000001', '-23:59:59', '-01:01:01', '2001-01-01 01:01:01'),
+('2001-01-01 01:01:01', '01:01:01', '-1 01:01:01', null),
+('2001-01-01 01:01:01', null, '-1 01:01:01', null),
+(null, null, null, null),
+('2001-01-01 01:01:01', '01:01:01', '1 01:01:01', '2001-01-01 01:01:01');
+SELECT ADDTIME(t1,t2) As ttt, ADDTIME(t2, t3) As qqq from test;
+ttt qqq
+2001-01-01 02:02:02 NULL
+2001-01-01 00:00:00 -25:01:00
+1997-12-31 00:00:00 -25:01:00
+2001-01-01 02:02:02 -24:00:00
+NULL NULL
+NULL NULL
+2001-01-01 02:02:02 26:02:02
+SELECT TIMEDIFF(t1,t4) As ttt, TIMEDIFF(t2, t3) As qqq from test;
+ttt qqq
+-744:00:00 NULL
+26305:01:02 22:58:58
+-26305:01:02 -22:58:58
+NULL 26:02:02
+NULL NULL
+NULL NULL
+00:00:00 -24:00:00
+drop table t1, test;
diff --git a/mysql-test/t/func_sapdb.test b/mysql-test/t/func_sapdb.test
new file mode 100644
index 00000000000..05e1f20fe1e
--- /dev/null
+++ b/mysql-test/t/func_sapdb.test
@@ -0,0 +1,97 @@
+--disable_warnings
+drop table if exists t1, test;
+--enable_warnings
+
+
+#
+# time functions
+#
+select extract(DAY_MICROSECOND FROM "1999-01-02 10:11:12.000123");
+select extract(HOUR_MICROSECOND FROM "1999-01-02 10:11:12.000123");
+select extract(MINUTE_MICROSECOND FROM "1999-01-02 10:11:12.000123");
+select extract(SECOND_MICROSECOND FROM "1999-01-02 10:11:12.000123");
+select extract(MICROSECOND FROM "1999-01-02 10:11:12.000123");
+select date_format("1997-12-31 23:59:59.000002", "%f");
+
+select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000 99:99:99.999999" DAY_MICROSECOND);
+select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99:99.999999" HOUR_MICROSECOND);
+select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99.999999" MINUTE_MICROSECOND);
+select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000.999999" SECOND_MICROSECOND);
+select date_add("1997-12-31 23:59:59.000002",INTERVAL "999999" MICROSECOND);
+
+select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND);
+select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND);
+select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND);
+select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND);
+select date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND);
+
+#Date functions
+select adddate("1997-12-31 23:59:59.000001", 10);
+select subdate("1997-12-31 23:59:59.000001", 10);
+
+select datediff("1997-12-31 23:59:59.000001","1997-12-30");
+select datediff("1997-11-31 23:59:59.000001","1997-12-31");
+select datediff("1997-11-31 23:59:59.000001",null);
+
+select weekofyear("1997-11-31 23:59:59.000001");
+
+select makedate(1997,1);
+select makedate(1997,0);
+
+#Time functions
+
+select addtime("1997-12-31 23:59:59.999999", "1 1:1:1.000002");
+select subtime("1997-12-31 23:59:59.000001", "1 1:1:1.000002");
+select addtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999");
+select subtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999");
+select subtime("01:00:00.999999", "02:00:00.999998");
+select subtime("02:01:01.999999", "01:01:01.999999");
+
+select timediff("1997-01-01 23:59:59.000001","1995-12-31 23:59:59.000002");
+select timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002");
+select timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002");
+select timediff("1997-12-31 23:59:59.000001","23:59:59.000001");
+select timediff("2000:01:01 00:00:00", "2000:01:01 00:00:00.1");
+
+select maketime(10,11,12);
+select maketime(25,11,12);
+select maketime(-25,11,12);
+
+#Extraction functions
+select timestamp("2001-12-01", "01:01:01.999999");
+select timestamp("2001-13-01", "01:01:01.000001");
+select timestamp("2001-12-01", "25:01:01");
+select day("1997-12-31 23:59:59.000001");
+select date("1997-12-31 23:59:59.000001");
+select date("1997-13-31 23:59:59.000001");
+select time("1997-12-31 23:59:59.000001");
+select time("1997-12-31 25:59:59.000001");
+select microsecond("1997-12-31 23:59:59.000001");
+
+create table t1
+select makedate(1997,1) as f1,
+ addtime(cast("1997-12-31 23:59:59.000001" as datetime), "1 1:1:1.000002") as f2,
+ addtime(cast("23:59:59.999999" as time) , "1 1:1:1.000002") as f3,
+ timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002") as f4,
+ timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002") as f5,
+ maketime(10,11,12) as f6,
+ timestamp("2001-12-01", "01:01:01") as f7,
+ date("1997-12-31 23:59:59.000001") as f8,
+ time("1997-12-31 23:59:59.000001") as f9;
+describe t1;
+select * from t1;
+
+create table test(t1 datetime, t2 time, t3 time, t4 datetime);
+insert into test values
+('2001-01-01 01:01:01', '01:01:01', null, '2001-02-01 01:01:01'),
+('2001-01-01 01:01:01', '-01:01:01', '-23:59:59', "1997-12-31 23:59:59.000001"),
+('1997-12-31 23:59:59.000001', '-23:59:59', '-01:01:01', '2001-01-01 01:01:01'),
+('2001-01-01 01:01:01', '01:01:01', '-1 01:01:01', null),
+('2001-01-01 01:01:01', null, '-1 01:01:01', null),
+(null, null, null, null),
+('2001-01-01 01:01:01', '01:01:01', '1 01:01:01', '2001-01-01 01:01:01');
+
+SELECT ADDTIME(t1,t2) As ttt, ADDTIME(t2, t3) As qqq from test;
+SELECT TIMEDIFF(t1,t4) As ttt, TIMEDIFF(t2, t3) As qqq from test;
+
+drop table t1, test;
diff --git a/sql/field.cc b/sql/field.cc
index a61654ed8f4..07682e36287 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -3150,11 +3150,13 @@ bool Field_time::get_time(TIME *ltime)
ltime->neg= 1;
tmp=-tmp;
}
+ ltime->day= 0;
ltime->hour= (int) (tmp/10000);
tmp-=ltime->hour*10000;
ltime->minute= (int) tmp/100;
ltime->second= (int) tmp % 100;
ltime->second_part=0;
+ ltime->time_type= TIMESTAMP_TIME;
return 0;
}
diff --git a/sql/item_create.cc b/sql/item_create.cc
index 3edec7fdab0..19edcaad3f6 100644
--- a/sql/item_create.cc
+++ b/sql/item_create.cc
@@ -678,3 +678,38 @@ Item *create_func_uncompressed_length(Item* a)
#endif
+Item *create_func_datediff(Item *a, Item *b)
+{
+ return new Item_func_minus(new Item_func_to_days(a),
+ new Item_func_to_days(b));
+}
+
+Item *create_func_weekofyear(Item *a)
+{
+ return new Item_func_week(a, new Item_int((char*) "0", 3, 1));
+}
+
+Item *create_func_makedate(Item* a,Item* b)
+{
+ return new Item_func_makedate(a, b);
+}
+
+Item *create_func_addtime(Item* a,Item* b)
+{
+ return new Item_func_add_time(a, b, 0);
+}
+
+Item *create_func_subtime(Item* a,Item* b)
+{
+ return new Item_func_add_time(a, b, 1);
+}
+
+Item *create_func_timediff(Item* a,Item* b)
+{
+ return new Item_func_timediff(a, b);
+}
+
+Item *create_func_maketime(Item* a,Item* b,Item* c)
+{
+ return new Item_func_maketime(a, b, c);
+}
diff --git a/sql/item_create.h b/sql/item_create.h
index b679c639244..f905e27ea00 100644
--- a/sql/item_create.h
+++ b/sql/item_create.h
@@ -146,3 +146,10 @@ Item *create_func_compress(Item *a);
Item *create_func_uncompress(Item *a);
Item *create_func_uncompressed_length(Item *a);
+Item *create_func_datediff(Item *a, Item *b);
+Item *create_func_weekofyear(Item *a);
+Item *create_func_makedate(Item* a,Item* b);
+Item *create_func_addtime(Item* a,Item* b);
+Item *create_func_subtime(Item* a,Item* b);
+Item *create_func_timediff(Item* a,Item* b);
+Item *create_func_maketime(Item* a,Item* b,Item* c);
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 61c869cddba..fd2bca591a7 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -55,6 +55,70 @@ static String day_names[] =
String("Sunday", &my_charset_latin1)
};
+enum date_time_format_types { TIME_ONLY= 0, TIME_MICROSECOND,
+ DATE_ONLY, DATE_TIME, DATE_TIME_MICROSECOND};
+
+typedef struct date_time_format {
+ const char* format_str;
+ uint length;
+};
+
+static struct date_time_format date_time_formats[]=
+{
+ {"%s%02d:%02d:%02d", 10},
+ {"%s%02d:%02d:%02d.%06d", 17},
+ {"%04d-%02d-%02d", 10},
+ {"%04d-%02d-%02d %02d:%02d:%02d", 19},
+ {"%04d-%02d-%02d %02d:%02d:%02d.%06d", 26}
+};
+
+
+String *make_datetime(String *str, TIME *ltime,
+ enum date_time_format_types format)
+{
+ char *buff;
+ CHARSET_INFO *cs= &my_charset_bin;
+ uint length= date_time_formats[format].length + 32;
+ const char* format_str= date_time_formats[format].format_str;
+
+ if (str->alloc(length))
+ return 0;
+
+ buff= (char*) str->ptr();
+ switch (format) {
+ case TIME_ONLY:
+ length= cs->cset->snprintf(cs, buff, length, format_str, ltime->neg ? "-" : "",
+ ltime->hour, ltime->minute, ltime->second);
+ break;
+ case TIME_MICROSECOND:
+ length= cs->cset->snprintf(cs, buff, length, format_str, ltime->neg ? "-" : "",
+ ltime->hour, ltime->minute, ltime->second,
+ ltime->second_part);
+ break;
+ case DATE_ONLY:
+ length= cs->cset->snprintf(cs, buff, length, format_str,
+ ltime->year, ltime->month, ltime->day);
+ break;
+ case DATE_TIME:
+ length= cs->cset->snprintf(cs, buff, length, format_str,
+ ltime->year, ltime->month, ltime->day,
+ ltime->hour, ltime->minute, ltime->second);
+ break;
+ case DATE_TIME_MICROSECOND:
+ length= cs->cset->snprintf(cs, buff, length, format_str,
+ ltime->year, ltime->month, ltime->day,
+ ltime->hour, ltime->minute, ltime->second,
+ ltime->second_part);
+ break;
+ default:
+ return 0;
+ }
+
+ str->length(length);
+ str->set_charset(cs);
+ return str;
+}
+
/*
** Get a array of positive numbers from a string object.
** Each number is separated by 1 non digit character
@@ -309,7 +373,7 @@ static bool get_interval_value(Item *args,interval_type int_type,
CHARSET_INFO *cs=str_value->charset();
bzero((char*) t,sizeof(*t));
- if ((int) int_type <= INTERVAL_SECOND)
+ if ((int) int_type <= INTERVAL_MICROSECOND)
{
value=(long) args->val_int();
if (args->null_value)
@@ -352,6 +416,9 @@ static bool get_interval_value(Item *args,interval_type int_type,
case INTERVAL_HOUR:
t->hour=value;
break;
+ case INTERVAL_MICROSECOND:
+ t->second_part=value;
+ break;
case INTERVAL_MINUTE:
t->minute=value;
break;
@@ -370,6 +437,15 @@ static bool get_interval_value(Item *args,interval_type int_type,
t->day=array[0];
t->hour=array[1];
break;
+ case INTERVAL_DAY_MICROSECOND:
+ if (get_interval_info(str,length,cs,5,array))
+ return (1);
+ t->day=array[0];
+ t->hour=array[1];
+ t->minute=array[2];
+ t->second=array[3];
+ t->second_part=array[4];
+ break;
case INTERVAL_DAY_MINUTE:
if (get_interval_info(str,length,cs,3,array))
return (1);
@@ -385,6 +461,14 @@ static bool get_interval_value(Item *args,interval_type int_type,
t->minute=array[2];
t->second=array[3];
break;
+ case INTERVAL_HOUR_MICROSECOND:
+ if (get_interval_info(str,length,cs,4,array))
+ return (1);
+ t->hour=array[0];
+ t->minute=array[1];
+ t->second=array[2];
+ t->second_part=array[3];
+ break;
case INTERVAL_HOUR_MINUTE:
if (get_interval_info(str,length,cs,2,array))
return (1);
@@ -398,12 +482,25 @@ static bool get_interval_value(Item *args,interval_type int_type,
t->minute=array[1];
t->second=array[2];
break;
+ case INTERVAL_MINUTE_MICROSECOND:
+ if (get_interval_info(str,length,cs,3,array))
+ return (1);
+ t->minute=array[0];
+ t->second=array[1];
+ t->second_part=array[2];
+ break;
case INTERVAL_MINUTE_SECOND:
if (get_interval_info(str,length,cs,2,array))
return (1);
t->minute=array[0];
t->second=array[1];
break;
+ case INTERVAL_SECOND_MICROSECOND:
+ if (get_interval_info(str,length,cs,2,array))
+ return (1);
+ t->second=array[0];
+ t->second_part=array[1];
+ break;
}
return 0;
}
@@ -687,6 +784,9 @@ uint Item_func_date_format::format_length(const String *format)
case 'T': /* time, 24-hour (hh:mm:ss) */
size += 8;
break;
+ case 'f': /* microseconds */
+ size += 6;
+ break;
case 'w': /* day (of the week), numeric */
case '%':
default:
@@ -844,6 +944,10 @@ String *Item_func_date_format::val_str(String *str)
sprintf(intbuff,"%d",l_time.day);
str->append(intbuff);
break;
+ case 'f':
+ sprintf(intbuff,"%06ld",l_time.second_part);
+ str->append(intbuff);
+ break;
case 'H':
sprintf(intbuff,"%02d",l_time.hour);
str->append(intbuff,2);
@@ -1005,7 +1109,7 @@ void Item_date_add_interval::fix_length_and_dec()
enum_field_types arg0_field_type;
set_charset(default_charset());
maybe_null=1;
- max_length=19*default_charset()->mbmaxlen;
+ max_length=26*default_charset()->mbmaxlen;
value.alloc(32);
/*
@@ -1051,27 +1155,43 @@ bool Item_date_add_interval::get_date(TIME *ltime, bool fuzzy_date)
null_value=0;
switch (int_type) {
case INTERVAL_SECOND:
+ case INTERVAL_SECOND_MICROSECOND:
+ case INTERVAL_MICROSECOND:
case INTERVAL_MINUTE:
case INTERVAL_HOUR:
+ case INTERVAL_MINUTE_MICROSECOND:
case INTERVAL_MINUTE_SECOND:
+ case INTERVAL_HOUR_MICROSECOND:
case INTERVAL_HOUR_SECOND:
case INTERVAL_HOUR_MINUTE:
+ case INTERVAL_DAY_MICROSECOND:
case INTERVAL_DAY_SECOND:
case INTERVAL_DAY_MINUTE:
case INTERVAL_DAY_HOUR:
- long sec,days,daynr;
+ long sec,days,daynr,microseconds,extra_sec;
ltime->time_type=TIMESTAMP_FULL; // Return full date
+ microseconds= ltime->second_part + sign*interval.second_part;
+ extra_sec= microseconds/1000000L;
+ microseconds= microseconds%1000000L;
sec=((ltime->day-1)*3600*24L+ltime->hour*3600+ltime->minute*60+
ltime->second +
sign*(interval.day*3600*24L +
- interval.hour*3600+interval.minute*60+interval.second));
+ interval.hour*3600+interval.minute*60+interval.second))+
+ extra_sec;
+
+ if (microseconds < 0)
+ {
+ microseconds+= 1000000L;
+ sec--;
+ }
days=sec/(3600*24L); sec=sec-days*3600*24L;
if (sec < 0)
{
days--;
sec+=3600*24L;
}
+ ltime->second_part= microseconds;
ltime->second=sec % 60;
ltime->minute=sec/60 % 60;
ltime->hour=sec/3600;
@@ -1124,34 +1244,21 @@ bool Item_date_add_interval::get_date(TIME *ltime, bool fuzzy_date)
String *Item_date_add_interval::val_str(String *str)
{
TIME ltime;
- CHARSET_INFO *cs=default_charset();
- uint32 l;
+ enum date_time_format_types format;
if (Item_date_add_interval::get_date(&ltime,0))
return 0;
+
if (ltime.time_type == TIMESTAMP_DATE)
- {
- l=11*cs->mbmaxlen+32;
- if (str->alloc(l))
- goto null_date;
- l=cs->cset->snprintf(cs,(char*) str->ptr(),l,"%04d-%02d-%02d",
- ltime.year,ltime.month,ltime.day);
- str->length(l);
- }
+ format= DATE_ONLY;
+ else if (ltime.second_part)
+ format= DATE_TIME_MICROSECOND;
else
- {
- l=20*cs->mbmaxlen+32;
- if (str->alloc(l))
- goto null_date;
- l=cs->cset->snprintf(cs,(char*) str->ptr(),l,"%04d-%02d-%02d %02d:%02d:%02d",
- ltime.year,ltime.month,ltime.day,
- ltime.hour,ltime.minute,ltime.second);
- str->length(l);
- }
- str->set_charset(cs);
- return str;
+ format= DATE_TIME;
+
+ if (make_datetime(str, &ltime, format))
+ return str;
- null_date:
null_value=1;
return 0;
}
@@ -1188,6 +1295,11 @@ void Item_extract::fix_length_and_dec()
case INTERVAL_MINUTE: max_length=2; date_value=0; break;
case INTERVAL_MINUTE_SECOND: max_length=4; date_value=0; break;
case INTERVAL_SECOND: max_length=2; date_value=0; break;
+ case INTERVAL_MICROSECOND: max_length=2; date_value=0; break;
+ case INTERVAL_DAY_MICROSECOND: max_length=20; date_value=0; break;
+ case INTERVAL_HOUR_MICROSECOND: max_length=13; date_value=0; break;
+ case INTERVAL_MINUTE_MICROSECOND: max_length=11; date_value=0; break;
+ case INTERVAL_SECOND_MICROSECOND: max_length=9; date_value=0; break;
}
}
@@ -1234,6 +1346,21 @@ longlong Item_extract::val_int()
case INTERVAL_MINUTE: return (long) ltime.minute*neg;
case INTERVAL_MINUTE_SECOND: return (long) (ltime.minute*100+ltime.second)*neg;
case INTERVAL_SECOND: return (long) ltime.second*neg;
+ case INTERVAL_MICROSECOND: return (long) ltime.second_part*neg;
+ case INTERVAL_DAY_MICROSECOND: return (((longlong)ltime.day*1000000L +
+ (longlong)ltime.hour*10000L +
+ ltime.minute*100 +
+ ltime.second)*1000000L +
+ ltime.second_part)*neg;
+ case INTERVAL_HOUR_MICROSECOND: return (((longlong)ltime.hour*10000L +
+ ltime.minute*100 +
+ ltime.second)*1000000L +
+ ltime.second_part)*neg;
+ case INTERVAL_MINUTE_MICROSECOND: return (((longlong)(ltime.minute*100+
+ ltime.second))*1000000L+
+ ltime.second_part)*neg;
+ case INTERVAL_SECOND_MICROSECOND: return ((longlong)ltime.second*1000000L+
+ ltime.second_part)*neg;
}
return 0; // Impossible
}
@@ -1247,3 +1374,358 @@ void Item_typecast::print(String *str)
str->append(func_name());
str->append(')');
}
+
+/*
+ MAKEDATE(a,b) is a date function that creates a date value
+ from a year and day value.
+*/
+
+String *Item_func_makedate::val_str(String *str)
+{
+ TIME l_time;
+ long daynr= args[1]->val_int();
+ long yearnr= args[0]->val_int();
+ long days;
+
+ if (args[0]->null_value || args[1]->null_value ||
+ yearnr < 0 || daynr <= 0)
+ goto null_date;
+
+ days= calc_daynr(yearnr,1,1) + daynr - 1;
+ if (days > 0 || days < 3652424L) // Day number from year 0 to 9999-12-31
+ {
+ null_value=0;
+ get_date_from_daynr(days,&l_time.year,&l_time.month,&l_time.day);
+ if (make_datetime(str, &l_time, DATE_ONLY))
+ return str;
+ }
+
+null_date:
+ null_value=1;
+ return 0;
+}
+
+
+void Item_func_add_time::fix_length_and_dec()
+{
+ enum_field_types arg0_field_type;
+ decimals=0;
+ max_length=26*my_charset_bin.mbmaxlen;
+
+ /*
+ The field type for the result of an Item_func_add_time function is defined as
+ follows:
+
+ - If first arg is a MYSQL_TYPE_DATETIME or MYSQL_TYPE_TIMESTAMP
+ result is MYSQL_TYPE_DATETIME
+ - If first arg is a MYSQL_TYPE_TIME result is MYSQL_TYPE_TIME
+ - Otherwise the result is MYSQL_TYPE_STRING
+ */
+
+ cached_field_type= MYSQL_TYPE_STRING;
+ arg0_field_type= args[0]->field_type();
+ if (arg0_field_type == MYSQL_TYPE_DATETIME ||
+ arg0_field_type == MYSQL_TYPE_TIMESTAMP)
+ cached_field_type= MYSQL_TYPE_DATETIME;
+ else if (arg0_field_type == MYSQL_TYPE_TIME)
+ cached_field_type= MYSQL_TYPE_TIME;
+}
+
+/*
+ ADDTIME(t,a) and SUBTIME(t,a) are time functions that calculate a time/datetime value
+
+ t: time_or_datetime_expression
+ a: time_expression
+
+ Result: Time value or datetime value
+*/
+
+String *Item_func_add_time::val_str(String *str)
+{
+ TIME l_time1, l_time2, l_time3;
+ bool is_time;
+ long microseconds, seconds, days= 0;
+ int l_sign= sign;
+
+ null_value=0;
+ if (args[0]->get_time(&l_time1) ||
+ args[1]->get_time(&l_time2) ||
+ l_time2.time_type == TIMESTAMP_FULL)
+ goto null_date;
+ is_time= (l_time1.time_type == TIMESTAMP_TIME);
+ l_time3.neg= 0;
+ if (is_time)
+ {
+ if ((l_time2.neg == l_time1.neg) && l_time1.neg)
+ l_time3.neg= 1;
+ }
+ if (l_time1.neg != l_time2.neg)
+ l_sign= -l_sign;
+
+ microseconds= l_time1.second_part + l_sign*l_time2.second_part;
+ seconds= (l_time1.hour*3600L + l_time1.minute*60L + l_time1.second +
+ (l_time2.day*86400L + l_time2.hour*3600L +
+ l_time2.minute*60L + l_time2.second)*l_sign);
+ if (is_time)
+ seconds+= l_time1.day*86400L;
+ else
+ days+= calc_daynr((uint) l_time1.year,(uint) l_time1.month, (uint) l_time1.day);
+ seconds= seconds + microseconds/1000000L;
+ microseconds= microseconds%1000000L;
+ days+= seconds/86400L;
+ seconds= seconds%86400L;
+
+ if (microseconds < 0)
+ {
+ microseconds+= 1000000L;
+ seconds--;
+ }
+ if (seconds < 0)
+ {
+ days+= seconds/86400L - 1;
+ seconds+= 86400L;
+ }
+ if (days < 0)
+ {
+ if (!is_time)
+ goto null_date;
+ if (microseconds)
+ {
+ microseconds= 1000000L - microseconds;
+ seconds++;
+ }
+ seconds= 86400L - seconds;
+ days= -(++days);
+ l_time3.neg= 1;
+ }
+
+ calc_time_from_sec(&l_time3, seconds, microseconds);
+ if (!is_time)
+ {
+ get_date_from_daynr(days,&l_time3.year,&l_time3.month,&l_time3.day);
+ if (l_time3.day &&
+ make_datetime(str, &l_time3, DATE_TIME_MICROSECOND))
+ return str;
+ goto null_date;
+ }
+
+ l_time3.hour+= days*24;
+ if (make_datetime(str, &l_time3, TIME_MICROSECOND))
+ return str;
+
+null_date:
+ null_value=1;
+ return 0;
+}
+
+/*
+ TIMEDIFF(t,s) is a time function that calculates the
+ time value between a start and end time.
+
+ t and s: time_or_datetime_expression
+ Result: Time value
+*/
+
+String *Item_func_timediff::val_str(String *str)
+{
+ longlong seconds;
+ long microseconds;
+ long days;
+ int l_sign= 1;
+ TIME l_time1 ,l_time2, l_time3;
+
+ null_value= 0;
+ if (args[0]->get_time(&l_time1) ||
+ args[1]->get_time(&l_time2) ||
+ l_time1.time_type != l_time2.time_type)
+ goto null_date;
+
+ if (l_time1.neg != l_time2.neg)
+ l_sign= -l_sign;
+
+ if (l_time1.time_type == TIMESTAMP_TIME) // Time value
+ days= l_time1.day - l_sign*l_time2.day;
+ else // DateTime value
+ days= (calc_daynr((uint) l_time1.year,
+ (uint) l_time1.month,
+ (uint) l_time1.day) -
+ l_sign*calc_daynr((uint) l_time2.year,
+ (uint) l_time2.month,
+ (uint) l_time2.day));
+
+ microseconds= l_time1.second_part - l_sign*l_time2.second_part;
+ seconds= ((longlong) days*86400L + l_time1.hour*3600L +
+ l_time1.minute*60L + l_time1.second + microseconds/1000000L -
+ (longlong)l_sign*(l_time2.hour*3600L+l_time2.minute*60L+l_time2.second));
+
+ l_time3.neg= 0;
+ if (seconds < 0)
+ {
+ seconds= -seconds;
+ l_time3.neg= 1;
+ }
+ else if (seconds == 0 && microseconds < 0)
+ {
+ microseconds= -microseconds;
+ l_time3.neg= 1;
+ }
+ if (microseconds < 0)
+ {
+ microseconds+= 1000000L;
+ seconds--;
+ }
+ if ((l_time2.neg == l_time1.neg) && l_time1.neg)
+ l_time3.neg= l_time3.neg ? 0 : 1;
+
+ calc_time_from_sec(&l_time3, seconds, microseconds);
+ if (make_datetime(str, &l_time3, TIME_MICROSECOND))
+ return str;
+
+null_date:
+ null_value=1;
+ return 0;
+}
+
+/*
+ MAKETIME(h,m,s) is a time function that calculates a time value
+ from the total number of hours, minutes, and seconds.
+ Result: Time value
+*/
+
+String *Item_func_maketime::val_str(String *str)
+{
+ TIME ltime;
+
+ long hour= args[0]->val_int();
+ long minute= args[1]->val_int();
+ long second= args[2]->val_int();
+
+ if ((null_value=(args[0]->null_value ||
+ args[1]->null_value ||
+ args[2]->null_value ||
+ minute > 59 || minute < 0 ||
+ second > 59 || second < 0)))
+ goto null_date;
+
+ ltime.neg= 0;
+ if (hour < 0)
+ {
+ ltime.neg= 1;
+ hour= -hour;
+ }
+ ltime.hour= (ulong)hour;
+ ltime.minute= (ulong)minute;
+ ltime.second= (ulong)second;
+ if (make_datetime(str, &ltime, TIME_ONLY))
+ return str;
+
+null_date:
+ return 0;
+}
+
+/*
+ TIMESTAMP(a,b) is a function ( extraction) that calculates a datetime value
+ comprising a date value, time value.
+
+ a: Date_or_datetime value
+ b: Time value
+ Result: Datetime value
+*/
+
+String *Item_func_timestamp::val_str(String *str)
+{
+ TIME l_time1 ,l_time2, l_time3;
+ long seconds;
+ long microseconds;
+ long days;
+ int l_sign;
+
+ if (get_arg0_date(&l_time1,1) ||
+ args[1]->get_time(&l_time2) ||
+ l_time1.time_type == TIMESTAMP_TIME ||
+ l_time2.time_type != TIMESTAMP_TIME)
+ goto null_date;
+
+ l_sign= l_time2.neg ? -1 : 1;
+ days= (calc_daynr((uint) l_time1.year,(uint) l_time1.month,
+ (uint) l_time1.day) + l_sign*l_time2.day);
+
+ microseconds= l_time1.second_part + l_sign*l_time2.second_part;
+ seconds= (l_time1.hour*3600L + l_time1.minute*60L + l_time1.second +
+ (l_time2.day*86400L + l_time2.hour*3600L +
+ l_time2.minute*60L + l_time2.second)*l_sign);
+ days+= seconds/86400L;
+ seconds%= 86400L;
+ if (microseconds < 0)
+ {
+ microseconds+= 1000000L;
+ seconds--;
+ }
+ if (seconds < 0)
+ {
+ days--;
+ seconds+= 86400L;
+ }
+ if (days < 0)
+ goto null_date;
+
+ calc_time_from_sec(&l_time3, seconds, microseconds);
+ get_date_from_daynr(days,&l_time3.year,&l_time3.month,&l_time3.day);
+ make_datetime(str, &l_time3, DATE_TIME_MICROSECOND);
+ return str;
+
+null_date:
+ null_value=1;
+ return 0;
+}
+
+/*
+ DATE(a) is a function ( extraction) that calculates a date value.
+
+ a: Datetime value
+ Result: Date value
+*/
+String *Item_func_date::val_str(String *str)
+{
+ TIME ltime;
+
+ if (!get_arg0_date(&ltime,1) &&
+ make_datetime(str, &ltime, DATE_ONLY))
+ return str;
+
+null_date:
+ null_value=1;
+ return 0;
+}
+
+/*
+ TIME(a) is a function ( extraction) that calculates a time value.
+
+ a: Datetime value
+ Result: Time value
+*/
+String *Item_func_time::val_str(String *str)
+{
+ TIME ltime;
+
+ if (!get_arg0_time(&ltime) &&
+ make_datetime(str, &ltime, TIME_MICROSECOND))
+ return str;
+
+ null_value=1;
+ return 0;
+}
+
+/*
+ MICROSECOND(a) is a function ( extraction) that extracts the microseconds from a.
+
+ a: Datetime or time value
+ Result: int value
+*/
+longlong Item_func_microsecond::val_int()
+{
+ TIME ltime;
+ if (!get_arg0_time(&ltime))
+ return ltime.second_part;
+ return 0;
+}
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index 87563cf9f47..ea29731fe35 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -478,9 +478,10 @@ public:
enum interval_type
{
INTERVAL_YEAR, INTERVAL_MONTH, INTERVAL_DAY, INTERVAL_HOUR, INTERVAL_MINUTE,
- INTERVAL_SECOND, INTERVAL_YEAR_MONTH, INTERVAL_DAY_HOUR, INTERVAL_DAY_MINUTE,
- INTERVAL_DAY_SECOND, INTERVAL_HOUR_MINUTE, INTERVAL_HOUR_SECOND,
- INTERVAL_MINUTE_SECOND
+ INTERVAL_SECOND, INTERVAL_MICROSECOND ,INTERVAL_YEAR_MONTH, INTERVAL_DAY_HOUR,
+ INTERVAL_DAY_MINUTE, INTERVAL_DAY_SECOND, INTERVAL_HOUR_MINUTE,
+ INTERVAL_HOUR_SECOND, INTERVAL_MINUTE_SECOND, INTERVAL_DAY_MICROSECOND,
+ INTERVAL_HOUR_MICROSECOND, INTERVAL_MINUTE_MICROSECOND, INTERVAL_SECOND_MICROSECOND
};
@@ -592,3 +593,157 @@ public:
return (new Field_datetime(maybe_null, name, t_arg, default_charset()));
}
};
+
+class Item_func_makedate :public Item_str_func
+{
+public:
+ Item_func_makedate(Item *a,Item *b) :Item_str_func(a,b) {}
+ String *val_str(String *str);
+ const char *func_name() const { return "makedate"; }
+ enum_field_types field_type() const { return MYSQL_TYPE_DATE; }
+ void fix_length_and_dec()
+ {
+ decimals=0;
+ max_length=8*my_charset_bin.mbmaxlen;
+ }
+ Field *tmp_table_field() { return result_field; }
+ Field *tmp_table_field(TABLE *t_arg)
+ {
+ return (new Field_date(maybe_null, name, t_arg, &my_charset_bin));
+ }
+};
+
+class Item_func_add_time :public Item_str_func
+{
+ int sign;
+ enum_field_types cached_field_type;
+
+public:
+ Item_func_add_time(Item *a, Item *b, bool neg_arg)
+ :Item_str_func(a, b) { sign= neg_arg ? -1 : 1; }
+ String *val_str(String *str);
+ const char *func_name() const { return "addtime"; }
+ enum_field_types field_type() const { return cached_field_type; }
+ void fix_length_and_dec();
+ Field *tmp_table_field() { return result_field; }
+ Field *tmp_table_field(TABLE *t_arg)
+ {
+ if (cached_field_type == MYSQL_TYPE_TIME)
+ return (new Field_time(maybe_null, name, t_arg, &my_charset_bin));
+ else if (cached_field_type == MYSQL_TYPE_DATETIME)
+ return (new Field_datetime(maybe_null, name, t_arg, &my_charset_bin));
+ return (new Field_string(max_length, maybe_null, name, t_arg, &my_charset_bin));
+ }
+};
+
+class Item_func_timediff :public Item_str_func
+{
+public:
+ Item_func_timediff(Item *a, Item *b)
+ :Item_str_func(a, b) {}
+ String *val_str(String *str);
+ const char *func_name() const { return "timediff"; }
+ enum_field_types field_type() const { return MYSQL_TYPE_TIME; }
+ void fix_length_and_dec()
+ {
+ decimals=0;
+ max_length=17*my_charset_bin.mbmaxlen;
+ }
+ Field *tmp_table_field() { return result_field; }
+ Field *tmp_table_field(TABLE *t_arg)
+ {
+ return (new Field_time(maybe_null, name, t_arg, &my_charset_bin));
+ }
+};
+
+class Item_func_maketime :public Item_str_func
+{
+public:
+ Item_func_maketime(Item *a, Item *b, Item *c)
+ :Item_str_func(a, b ,c) {}
+ String *val_str(String *str);
+ const char *func_name() const { return "maketime"; }
+ enum_field_types field_type() const { return MYSQL_TYPE_TIME; }
+ void fix_length_and_dec()
+ {
+ decimals=0;
+ max_length=8*my_charset_bin.mbmaxlen;
+ }
+ Field *tmp_table_field() { return result_field; }
+ Field *tmp_table_field(TABLE *t_arg)
+ {
+ return (new Field_time(maybe_null, name, t_arg, &my_charset_bin));
+ }
+};
+
+class Item_func_timestamp :public Item_str_func
+{
+public:
+ Item_func_timestamp(Item *a, Item *b) :Item_str_func(a, b) {}
+ String *val_str(String *str);
+ const char *func_name() const { return "timestamp"; }
+ enum_field_types field_type() const { return MYSQL_TYPE_DATETIME; }
+ void fix_length_and_dec()
+ {
+ decimals=0;
+ max_length=26*my_charset_bin.mbmaxlen;
+ }
+ Field *tmp_table_field() { return result_field; }
+ Field *tmp_table_field(TABLE *t_arg)
+ {
+ return (new Field_datetime(maybe_null, name, t_arg, &my_charset_bin));
+ }
+};
+
+class Item_func_date :public Item_str_func
+{
+public:
+ Item_func_date(Item *a)
+ :Item_str_func(a) {}
+ String *val_str(String *str);
+ const char *func_name() const { return "date"; }
+ enum_field_types field_type() const { return MYSQL_TYPE_DATE; }
+ void fix_length_and_dec()
+ {
+ decimals=0;
+ max_length=10*my_charset_bin.mbmaxlen;
+ }
+ Field *tmp_table_field() { return result_field; }
+ Field *tmp_table_field(TABLE *t_arg)
+ {
+ return (new Field_date(maybe_null, name, t_arg, &my_charset_bin));
+ }
+};
+
+class Item_func_time :public Item_str_func
+{
+public:
+ Item_func_time(Item *a)
+ :Item_str_func(a) {}
+ String *val_str(String *str);
+ const char *func_name() const { return "time"; }
+ enum_field_types field_type() const { return MYSQL_TYPE_TIME; }
+ void fix_length_and_dec()
+ {
+ decimals=0;
+ max_length=15*my_charset_bin.mbmaxlen;
+ }
+ Field *tmp_table_field() { return result_field; }
+ Field *tmp_table_field(TABLE *t_arg)
+ {
+ return (new Field_time(maybe_null, name, t_arg, &my_charset_bin));
+ }
+};
+
+class Item_func_microsecond :public Item_int_func
+{
+public:
+ Item_func_microsecond(Item *a) :Item_int_func(a) {}
+ longlong val_int();
+ const char *func_name() const { return "microsecond"; }
+ void fix_length_and_dec()
+ {
+ decimals=0;
+ maybe_null=1;
+ }
+};
diff --git a/sql/lex.h b/sql/lex.h
index e89c9f51520..8abf96645ec 100644
--- a/sql/lex.h
+++ b/sql/lex.h
@@ -116,6 +116,7 @@ static SYMBOL symbols[] = {
{ "DATETIME", SYM(DATETIME),0,0},
{ "DAY", SYM(DAY_SYM),0,0},
{ "DAY_HOUR", SYM(DAY_HOUR_SYM),0,0},
+ { "DAY_MICROSECOND", SYM(DAY_MICROSECOND_SYM),0,0},
{ "DAY_MINUTE", SYM(DAY_MINUTE_SYM),0,0},
{ "DAY_SECOND", SYM(DAY_SECOND_SYM),0,0},
{ "DEC", SYM(DECIMAL_SYM),0,0},
@@ -186,6 +187,7 @@ static SYMBOL symbols[] = {
{ "HELP", SYM(HELP_SYM),0,0},
{ "HIGH_PRIORITY", SYM(HIGH_PRIORITY),0,0},
{ "HOUR", SYM(HOUR_SYM),0,0},
+ { "HOUR_MICROSECOND", SYM(HOUR_MICROSECOND_SYM),0,0},
{ "HOUR_MINUTE", SYM(HOUR_MINUTE_SYM),0,0},
{ "HOUR_SECOND", SYM(HOUR_SECOND_SYM),0,0},
{ "HOSTS", SYM(HOSTS_SYM),0,0},
@@ -258,9 +260,11 @@ static SYMBOL symbols[] = {
{ "MERGE", SYM(MERGE_SYM),0,0},
{ "MEDIUM", SYM(MEDIUM_SYM),0,0},
{ "MEMORY", SYM(MEMORY_SYM),0,0},
+ { "MICROSECOND", SYM(MICROSECOND_SYM),0,0},
{ "MIDDLEINT", SYM(MEDIUMINT),0,0}, /* For powerbuilder */
{ "MIN_ROWS", SYM(MIN_ROWS),0,0},
{ "MINUTE", SYM(MINUTE_SYM),0,0},
+ { "MINUTE_MICROSECOND", SYM(MINUTE_MICROSECOND_SYM),0,0},
{ "MINUTE_SECOND", SYM(MINUTE_SECOND_SYM),0,0},
{ "MOD", SYM(MOD_SYM),0,0},
{ "MODE", SYM(MODE_SYM),0,0},
@@ -336,6 +340,7 @@ static SYMBOL symbols[] = {
{ "ROWS", SYM(ROWS_SYM),0,0},
{ "RTREE", SYM(RTREE_SYM),0,0},
{ "SECOND", SYM(SECOND_SYM),0,0},
+ { "SECOND_MICROSECOND", SYM(SECOND_MICROSECOND_SYM),0,0},
{ "SEPARATOR", SYM(SEPARATOR_SYM),0,0},
{ "SELECT", SYM(SELECT_SYM),0,0},
{ "SERIAL", SYM(SERIAL_SYM),0,0},
@@ -425,7 +430,8 @@ static SYMBOL symbols[] = {
static SYMBOL sql_functions[] = {
{ "ABS", SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_abs)},
{ "ACOS", SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_acos)},
- { "ADDDATE", SYM(DATE_ADD_INTERVAL),0,0},
+ { "ADDDATE", SYM(ADDDATE_SYM),0,0},
+ { "ADDTIME", SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_addtime)},
{ "AES_ENCRYPT", SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_aes_encrypt)},
{ "AES_DECRYPT", SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_aes_decrypt)},
{ "AREA", SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_area)},
@@ -467,6 +473,7 @@ static SYMBOL sql_functions[] = {
{ "CURDATE", SYM(CURDATE),0,0},
{ "CURTIME", SYM(CURTIME),0,0},
{ "DATE_ADD", SYM(DATE_ADD_INTERVAL),0,0},
+ { "DATEDIFF", SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_datediff)},
{ "DATE_FORMAT", SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_date_format)},
{ "DATE_SUB", SYM(DATE_SUB_INTERVAL),0,0},
{ "DAYNAME", SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_dayname)},
@@ -542,6 +549,8 @@ static SYMBOL sql_functions[] = {
{ "LPAD", SYM(FUNC_ARG3),0,CREATE_FUNC(create_func_lpad)},
{ "LTRIM", SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_ltrim)},
{ "MAKE_SET", SYM(MAKE_SET_SYM),0,0},
+ { "MAKEDATE", SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_makedate)},
+ { "MAKETIME", SYM(FUNC_ARG3),0,CREATE_FUNC(create_func_maketime)},
{ "MASTER_POS_WAIT", SYM(MASTER_POS_WAIT),0,0},
{ "MAX", SYM(MAX_SYM),0,0},
{ "MBRCONTAINS", SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_contains)},
@@ -602,7 +611,7 @@ static SYMBOL sql_functions[] = {
{ "RTRIM", SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_rtrim)},
{ "SEC_TO_TIME", SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_sec_to_time)},
{ "SESSION_USER", SYM(USER),0,0},
- { "SUBDATE", SYM(DATE_SUB_INTERVAL),0,0},
+ { "SUBDATE", SYM(SUBDATE_SYM),0,0},
{ "SIGN", SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_sign)},
{ "SIN", SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_sin)},
{ "SHA", SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_sha)},
@@ -617,12 +626,14 @@ static SYMBOL sql_functions[] = {
{ "STRCMP", SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_strcmp)},
{ "SUBSTRING", SYM(SUBSTRING),0,0},
{ "SUBSTRING_INDEX", SYM(SUBSTRING_INDEX),0,0},
+ { "SUBTIME", SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_subtime)},
{ "SUM", SYM(SUM_SYM),0,0},
{ "SYSDATE", SYM(NOW_SYM),0,0},
{ "SYSTEM_USER", SYM(USER),0,0},
{ "TAN", SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_tan)},
{ "TIME_FORMAT", SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_time_format)},
{ "TIME_TO_SEC", SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_time_to_sec)},
+ { "TIMEDIFF", SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_timediff)},
{ "TO_DAYS", SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_to_days)},
{ "TOUCHES", SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_touches)},
{ "TRIM", SYM(TRIM),0,0},
@@ -637,6 +648,7 @@ static SYMBOL sql_functions[] = {
{ "VERSION", SYM(FUNC_ARG0),0,CREATE_FUNC(create_func_version)},
{ "WEEK", SYM(WEEK_SYM),0,0},
{ "WEEKDAY", SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_weekday)},
+ { "WEEKOFYEAR", SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_weekofyear)},
{ "WITHIN", SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_within)},
{ "X", SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_x)},
{ "Y", SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_y)},
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index a59f1d4b81a..9aad06bd21d 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -844,6 +844,7 @@ longlong str_to_datetime(const char *str,uint length,bool fuzzy_date);
timestamp_type str_to_TIME(const char *str, uint length, TIME *l_time,
bool fuzzy_date);
void localtime_to_TIME(TIME *to, struct tm *from);
+void calc_time_from_sec(TIME *to, long seconds, long microseconds);
int test_if_number(char *str,int *res,bool allow_wildcards);
void change_byte(byte *,uint,char,char);
diff --git a/sql/protocol.cc b/sql/protocol.cc
index 7abbf3ce85b..b214b5627d5 100644
--- a/sql/protocol.cc
+++ b/sql/protocol.cc
@@ -862,6 +862,8 @@ bool Protocol_simple::store(TIME *tm)
(int) tm->hour,
(int) tm->minute,
(int) tm->second));
+ if (tm->second_part)
+ length+= my_sprintf(buff+length,(buff+length, ".%06d", (int)tm->second_part));
return net_store_data((char*) buff, length);
}
@@ -898,6 +900,8 @@ bool Protocol_simple::store_time(TIME *tm)
(long) day*24L+(long) tm->hour,
(int) tm->minute,
(int) tm->second));
+ if (tm->second_part)
+ length+= my_sprintf(buff+length,(buff+length, ".%06d", (int)tm->second_part));
return net_store_data((char*) buff, length);
}
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 1002d06be88..53fe2d4c123 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -163,6 +163,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
%token VARIANCE_SYM
%token STOP_SYM
%token SUM_SYM
+%token ADDDATE_SYM
%token SUPER_SYM
%token TRUNCATE_SYM
%token UNLOCK_SYM
@@ -430,6 +431,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
%token VARYING
%token ZEROFILL
+%token ADDDATE_SYM
%token AGAINST
%token ATAN
%token BETWEEN_SYM
@@ -444,6 +446,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
%token DATE_ADD_INTERVAL
%token DATE_SUB_INTERVAL
%token DAY_HOUR_SYM
+%token DAY_MICROSECOND_SYM
%token DAY_MINUTE_SYM
%token DAY_SECOND_SYM
%token DAY_SYM
@@ -466,6 +469,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
%token GEOMETRYCOLLECTION
%token GROUP_CONCAT_SYM
%token GROUP_UNIQUE_USERS
+%token HOUR_MICROSECOND_SYM
%token HOUR_MINUTE_SYM
%token HOUR_SECOND_SYM
%token HOUR_SYM
@@ -480,6 +484,8 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
%token LOCATE
%token MAKE_SET_SYM
%token MASTER_POS_WAIT
+%token MICROSECOND_SYM
+%token MINUTE_MICROSECOND_SYM
%token MINUTE_SECOND_SYM
%token MINUTE_SYM
%token MODE_SYM
@@ -504,7 +510,9 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
%token RIGHT
%token ROUND
%token SECOND_SYM
+%token SECOND_MICROSECOND_SYM
%token SHARE_SYM
+%token SUBDATE_SYM
%token SUBSTRING
%token SUBSTRING_INDEX
%token TRIM
@@ -2255,6 +2263,10 @@ simple_expr:
{ $$= ((Item*(*)(Item*,Item*))($1.symbol->create_func))($3,$5);}
| FUNC_ARG3 '(' expr ',' expr ',' expr ')'
{ $$= ((Item*(*)(Item*,Item*,Item*))($1.symbol->create_func))($3,$5,$7);}
+ | ADDDATE_SYM '(' expr ',' expr ')'
+ { $$= new Item_date_add_interval($3, $5, INTERVAL_DAY, 0);}
+ | ADDDATE_SYM '(' expr ',' INTERVAL_SYM expr interval ')'
+ { $$= new Item_date_add_interval($3, $6, $7, 0); }
| ATAN '(' expr ')'
{ $$= new Item_func_atan($3); }
| ATAN '(' expr ',' expr ')'
@@ -2289,6 +2301,10 @@ simple_expr:
$$= new Item_func_database();
Lex->safe_to_cache_query=0;
}
+ | DATE_SYM '(' expr ')'
+ { $$= new Item_func_date($3); }
+ | DAY_SYM '(' expr ')'
+ { $$= new Item_func_dayofmonth($3); }
| ELT_FUNC '(' expr ',' expr_list ')'
{ $$= new Item_func_elt($3, *$5); }
| MAKE_SET_SYM '(' expr ',' expr_list ')'
@@ -2405,6 +2421,8 @@ simple_expr:
$$= new Item_master_pos_wait($3, $5, $7);
Lex->safe_to_cache_query=0;
}
+ | MICROSECOND_SYM '(' expr ')'
+ { $$= new Item_func_microsecond($3); }
| MINUTE_SYM '(' expr ')'
{ $$= new Item_func_minute($3); }
| MOD_SYM '(' expr ',' expr ')'
@@ -2466,6 +2484,10 @@ simple_expr:
| ROUND '(' expr ')'
{ $$= new Item_func_round($3, new Item_int((char*)"0",0,1),0); }
| ROUND '(' expr ',' expr ')' { $$= new Item_func_round($3,$5,0); }
+ | SUBDATE_SYM '(' expr ',' expr ')'
+ { $$= new Item_date_add_interval($3, $5, INTERVAL_DAY, 1);}
+ | SUBDATE_SYM '(' expr ',' INTERVAL_SYM expr interval ')'
+ { $$= new Item_date_add_interval($3, $6, $7, 1); }
| SECOND_SYM '(' expr ')'
{ $$= new Item_func_second($3); }
| SUBSTRING '(' expr ',' expr ',' expr ')'
@@ -2478,6 +2500,10 @@ simple_expr:
{ $$= new Item_func_substr($3,$5); }
| SUBSTRING_INDEX '(' expr ',' expr ',' expr ')'
{ $$= new Item_func_substr_index($3,$5,$7); }
+ | TIME_SYM '(' expr ')'
+ { $$= new Item_func_time($3); }
+ | TIMESTAMP '(' expr ',' expr ')'
+ { $$= new Item_func_timestamp($3, $5); }
| TRIM '(' expr ')'
{ $$= new Item_func_trim($3,new Item_string(" ",1,default_charset_info)); }
| TRIM '(' LEADING opt_pad FROM expr ')'
@@ -2892,15 +2918,20 @@ using_list:
interval:
DAY_HOUR_SYM { $$=INTERVAL_DAY_HOUR; }
+ | DAY_MICROSECOND_SYM { $$=INTERVAL_DAY_MICROSECOND; }
| DAY_MINUTE_SYM { $$=INTERVAL_DAY_MINUTE; }
| DAY_SECOND_SYM { $$=INTERVAL_DAY_SECOND; }
| DAY_SYM { $$=INTERVAL_DAY; }
+ | HOUR_MICROSECOND_SYM { $$=INTERVAL_HOUR_MICROSECOND; }
| HOUR_MINUTE_SYM { $$=INTERVAL_HOUR_MINUTE; }
| HOUR_SECOND_SYM { $$=INTERVAL_HOUR_SECOND; }
| HOUR_SYM { $$=INTERVAL_HOUR; }
+ | MICROSECOND_SYM { $$=INTERVAL_MICROSECOND; }
+ | MINUTE_MICROSECOND_SYM { $$=INTERVAL_MINUTE_MICROSECOND; }
| MINUTE_SECOND_SYM { $$=INTERVAL_MINUTE_SECOND; }
| MINUTE_SYM { $$=INTERVAL_MINUTE; }
| MONTH_SYM { $$=INTERVAL_MONTH; }
+ | SECOND_MICROSECOND_SYM { $$=INTERVAL_SECOND_MICROSECOND; }
| SECOND_SYM { $$=INTERVAL_SECOND; }
| YEAR_MONTH_SYM { $$=INTERVAL_YEAR_MONTH; }
| YEAR_SYM { $$=INTERVAL_YEAR; };
@@ -4191,6 +4222,7 @@ user:
keyword:
ACTION {}
+ | ADDDATE_SYM {}
| AFTER_SYM {}
| AGAINST {}
| AGGREGATE_SYM {}
@@ -4349,6 +4381,7 @@ keyword:
| STATUS_SYM {}
| STOP_SYM {}
| STRING_SYM {}
+ | SUBDATE_SYM {}
| SUBJECT_SYM {}
| SUPER_SYM {}
| TEMPORARY {}
diff --git a/sql/time.cc b/sql/time.cc
index eba664a690d..81624ba7287 100644
--- a/sql/time.cc
+++ b/sql/time.cc
@@ -567,7 +567,7 @@ bool str_to_time(const char *str,uint length,TIME *l_time)
/* Get fractional second part */
if ((end-str) >= 2 && *str == '.' && my_isdigit(&my_charset_latin1,str[1]))
{
- uint field_length=3;
+ uint field_length=5;
str++; value=(uint) (uchar) (*str - '0');
while (++str != end &&
my_isdigit(&my_charset_latin1,str[0]) &&
@@ -590,6 +590,7 @@ bool str_to_time(const char *str,uint length,TIME *l_time)
l_time->minute=date[2];
l_time->second=date[3];
l_time->second_part=date[4];
+ l_time->time_type= TIMESTAMP_TIME;
/* Check if there is garbage at end of the TIME specification */
if (str != end && current_thd->count_cuted_fields)
@@ -622,3 +623,13 @@ void localtime_to_TIME(TIME *to, struct tm *from)
to->minute= (int) from->tm_min;
to->second= (int) from->tm_sec;
}
+
+void calc_time_from_sec(TIME *to, long seconds, long microseconds)
+{
+ long t_seconds;
+ to->hour= seconds/3600L;
+ t_seconds= seconds%3600L;
+ to->minute= t_seconds/60L;
+ to->second= t_seconds%60L;
+ to->second_part= microseconds;
+}
View Lorry Controller Status