1
/* Copyright (C) 2000-2003 MySQL AB
3
This program is free software; you can redistribute it and/or modify
4
it under the terms of the GNU General Public License as published by
5
the Free Software Foundation; version 2 of the License.
7
This program is distributed in the hope that it will be useful,
8
but WITHOUT ANY WARRANTY; without even the implied warranty of
9
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
10
GNU General Public License for more details.
12
You should have received a copy of the GNU General Public License
13
along with this program; if not, write to the Free Software
14
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
21
This file defines all time functions
24
Move month and days to language files
27
#ifdef USE_PRAGMA_IMPLEMENTATION
28
#pragma implementation // gcc: Class implementation
31
#include "mysql_priv.h"
35
/** Day number for Dec 31st, 9999. */
36
#define MAX_DAY_NUMBER 3652424L
41
- Replace the switch with a function that should be called for each
43
- Remove sprintf and opencode the conversion, like we do in
46
The reason for this functions existence is that as we don't have a
47
way to know if a datetime/time value has microseconds in them
48
we are now only adding microseconds to the output if the
49
value has microseconds.
51
We can't use a standard make_date_time() for this as we don't know
52
if someone will use %f in the format specifier in which case we would get
53
the microseconds twice.
56
static bool make_datetime(date_time_format_types format, MYSQL_TIME *ltime,
60
CHARSET_INFO *cs= &my_charset_bin;
61
uint length= MAX_DATE_STRING_REP_LENGTH;
63
if (str->alloc(length))
65
buff= (char*) str->ptr();
69
length= cs->cset->snprintf(cs, buff, length, "%s%02d:%02d:%02d",
70
ltime->neg ? "-" : "",
71
ltime->hour, ltime->minute, ltime->second);
73
case TIME_MICROSECOND:
74
length= cs->cset->snprintf(cs, buff, length, "%s%02d:%02d:%02d.%06ld",
75
ltime->neg ? "-" : "",
76
ltime->hour, ltime->minute, ltime->second,
80
length= cs->cset->snprintf(cs, buff, length, "%04d-%02d-%02d",
81
ltime->year, ltime->month, ltime->day);
84
length= cs->cset->snprintf(cs, buff, length,
85
"%04d-%02d-%02d %02d:%02d:%02d",
86
ltime->year, ltime->month, ltime->day,
87
ltime->hour, ltime->minute, ltime->second);
89
case DATE_TIME_MICROSECOND:
90
length= cs->cset->snprintf(cs, buff, length,
91
"%04d-%02d-%02d %02d:%02d:%02d.%06ld",
92
ltime->year, ltime->month, ltime->day,
93
ltime->hour, ltime->minute, ltime->second,
105
Wrapper over make_datetime() with validation of the input MYSQL_TIME value
108
see make_datetime() for more information
111
1 if there was an error during converion
115
static bool make_datetime_with_warn(date_time_format_types format, MYSQL_TIME *ltime,
120
if (make_datetime(format, ltime, str))
122
if (check_time_range(ltime, &warning))
127
make_truncated_value_warning(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
128
str->ptr(), str->length(),
129
MYSQL_TIMESTAMP_TIME, NullS);
130
return make_datetime(format, ltime, str);
135
Wrapper over make_time() with validation of the input MYSQL_TIME value
138
see make_time() for more info
141
1 if there was an error during conversion
145
static bool make_time_with_warn(const DATE_TIME_FORMAT *format,
146
MYSQL_TIME *l_time, String *str)
149
make_time(format, l_time, str);
150
if (check_time_range(l_time, &warning))
154
make_truncated_value_warning(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
155
str->ptr(), str->length(),
156
MYSQL_TIMESTAMP_TIME, NullS);
157
make_time(format, l_time, str);
165
Convert seconds to MYSQL_TIME value with overflow checking
169
seconds number of seconds
170
unsigned_flag 1, if 'seconds' is unsigned, 0, otherwise
171
ltime output MYSQL_TIME value
174
If the 'seconds' argument is inside MYSQL_TIME data range, convert it to a
176
Otherwise, truncate the resulting value to the nearest endpoint, and
177
produce a warning message.
180
1 if the value was truncated during conversion
184
static bool sec_to_time(longlong seconds, bool unsigned_flag, MYSQL_TIME *ltime)
188
bzero((char *)ltime, sizeof(*ltime));
195
if (seconds < -3020399)
199
else if (seconds > 3020399)
202
sec= (uint) ((ulonglong) seconds % 3600);
203
ltime->hour= (uint) (seconds/3600);
204
ltime->minute= sec/60;
205
ltime->second= sec % 60;
210
ltime->hour= TIME_MAX_HOUR;
211
ltime->minute= TIME_MAX_MINUTE;
212
ltime->second= TIME_MAX_SECOND;
215
int len= (int)(longlong10_to_str(seconds, buf, unsigned_flag ? 10 : -10)
217
make_truncated_value_warning(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
218
buf, len, MYSQL_TIMESTAMP_TIME,
226
Date formats corresponding to compound %r and %T conversion specifiers
228
Note: We should init at least first element of "positions" array
229
(first member) or hpux11 compiler will die horribly.
231
static DATE_TIME_FORMAT time_ampm_format= {{0}, '\0', 0,
232
{(char *)"%I:%i:%S %p", 11}};
233
static DATE_TIME_FORMAT time_24hrs_format= {{0}, '\0', 0,
234
{(char *)"%H:%i:%S", 8}};
237
Extract datetime value to MYSQL_TIME struct from string value
238
according to format string.
240
@param format date/time format specification
241
@param val String to decode
242
@param length Length of string
243
@param l_time Store result here
244
@param cached_timestamp_type It uses to get an appropriate warning
245
in the case when the value is truncated.
246
@param sub_pattern_end if non-zero then we are parsing string which
247
should correspond compound specifier (like %T or
248
%r) and this parameter is pointer to place where
249
pointer to end of string matching this specifier
253
Possibility to parse strings matching to patterns equivalent to compound
254
specifiers is mainly intended for use from inside of this function in
255
order to understand %T and %r conversion specifiers, so number of
256
conversion specifiers that can be used in such sub-patterns is limited.
257
Also most of checks are skipped in this case.
260
If one adds new format specifiers to this function he should also
261
consider adding them to get_date_time_result_type() function.
269
static bool extract_date_time(DATE_TIME_FORMAT *format,
270
const char *val, uint length, MYSQL_TIME *l_time,
271
timestamp_type cached_timestamp_type,
272
const char **sub_pattern_end,
273
const char *date_time_type)
275
int weekday= 0, yearday= 0, daypart= 0;
278
int strict_week_number_year= -1;
281
bool UNINIT_VAR(sunday_first_n_first_week_non_iso);
282
bool UNINIT_VAR(strict_week_number);
283
bool UNINIT_VAR(strict_week_number_year_type);
284
const char *val_begin= val;
285
const char *val_end= val + length;
286
const char *ptr= format->format.str;
287
const char *end= ptr + format->format.length;
288
CHARSET_INFO *cs= &my_charset_bin;
289
DBUG_ENTER("extract_date_time");
291
if (!sub_pattern_end)
292
bzero((char*) l_time, sizeof(*l_time));
294
for (; ptr != end && val != val_end; ptr++)
296
/* Skip pre-space between each argument */
297
while (val != val_end && my_isspace(cs, *val))
300
if (*ptr == '%' && ptr+1 != end)
307
val_len= (uint) (val_end - val);
311
tmp= (char*) val + min(4, val_len);
312
l_time->year= (int) my_strtoll10(val, &tmp, &error);
313
if ((int) (tmp-val) <= 2)
314
l_time->year= year_2000_handling(l_time->year);
318
tmp= (char*) val + min(2, val_len);
319
l_time->year= (int) my_strtoll10(val, &tmp, &error);
321
l_time->year= year_2000_handling(l_time->year);
327
tmp= (char*) val + min(2, val_len);
328
l_time->month= (int) my_strtoll10(val, &tmp, &error);
332
if ((l_time->month= check_word(my_locale_en_US.month_names,
333
val, val_end, &val)) <= 0)
337
if ((l_time->month= check_word(my_locale_en_US.ab_month_names,
338
val, val_end, &val)) <= 0)
344
tmp= (char*) val + min(2, val_len);
345
l_time->day= (int) my_strtoll10(val, &tmp, &error);
349
tmp= (char*) val + min(2, val_len);
350
l_time->day= (int) my_strtoll10(val, &tmp, &error);
351
/* Skip 'st, 'nd, 'th .. */
352
val= tmp + min((int) (val_end-tmp), 2);
363
tmp= (char*) val + min(2, val_len);
364
l_time->hour= (int) my_strtoll10(val, &tmp, &error);
370
tmp= (char*) val + min(2, val_len);
371
l_time->minute= (int) my_strtoll10(val, &tmp, &error);
378
tmp= (char*) val + min(2, val_len);
379
l_time->second= (int) my_strtoll10(val, &tmp, &error);
385
tmp= (char*) val_end;
387
tmp= (char*) val + 6;
388
l_time->second_part= (int) my_strtoll10(val, &tmp, &error);
389
frac_part= 6 - (int) (tmp - val);
391
l_time->second_part*= (ulong) log_10_int[frac_part];
397
if (val_len < 2 || ! usa_time)
399
if (!my_strnncoll(&my_charset_latin1,
400
(const uchar *) val, 2,
401
(const uchar *) "PM", 2))
403
else if (my_strnncoll(&my_charset_latin1,
404
(const uchar *) val, 2,
405
(const uchar *) "AM", 2))
412
if ((weekday= check_word(my_locale_en_US.day_names, val, val_end, &val)) <= 0)
416
if ((weekday= check_word(my_locale_en_US.ab_day_names, val, val_end, &val)) <= 0)
420
tmp= (char*) val + 1;
421
if ((weekday= (int) my_strtoll10(val, &tmp, &error)) < 0 ||
424
/* We should use the same 1 - 7 scale for %w as for %W */
430
tmp= (char*) val + min(val_len, 3);
431
yearday= (int) my_strtoll10(val, &tmp, &error);
440
sunday_first_n_first_week_non_iso= (*ptr=='U' || *ptr== 'V');
441
strict_week_number= (*ptr=='V' || *ptr=='v');
442
tmp= (char*) val + min(val_len, 2);
443
if ((week_number= (int) my_strtoll10(val, &tmp, &error)) < 0 ||
444
(strict_week_number && !week_number) ||
450
/* Year used with 'strict' %V and %v week numbers */
453
strict_week_number_year_type= (*ptr=='X');
454
tmp= (char*) val + min(4, val_len);
455
strict_week_number_year= (int) my_strtoll10(val, &tmp, &error);
459
/* Time in AM/PM notation */
462
We can't just set error here, as we don't want to generate two
463
warnings in case of errors
465
if (extract_date_time(&time_ampm_format, val,
466
(uint)(val_end - val), l_time,
467
cached_timestamp_type, &val, "time"))
471
/* Time in 24-hour notation */
473
if (extract_date_time(&time_24hrs_format, val,
474
(uint)(val_end - val), l_time,
475
cached_timestamp_type, &val, "time"))
479
/* Conversion specifiers that match classes of characters */
481
while (my_ispunct(cs, *val) && val != val_end)
485
while (my_isalpha(cs, *val) && val != val_end)
489
while (my_isdigit(cs, *val) && val != val_end)
495
if (error) // Error from my_strtoll10
498
else if (!my_isspace(cs, *ptr))
507
if (l_time->hour > 12 || l_time->hour < 1)
509
l_time->hour= l_time->hour%12+daypart;
513
If we are recursively called for parsing string matching compound
514
specifiers we are already done.
518
*sub_pattern_end= val;
525
days= calc_daynr(l_time->year,1,1) + yearday - 1;
526
if (days <= 0 || days > MAX_DAY_NUMBER)
528
get_date_from_daynr(days,&l_time->year,&l_time->month,&l_time->day);
531
if (week_number >= 0 && weekday)
537
%V,%v require %X,%x resprectively,
538
%U,%u should be used with %Y and not %X or %x
540
if ((strict_week_number &&
541
(strict_week_number_year < 0 ||
542
strict_week_number_year_type != sunday_first_n_first_week_non_iso)) ||
543
(!strict_week_number && strict_week_number_year >= 0))
546
/* Number of days since year 0 till 1st Jan of this year */
547
days= calc_daynr((strict_week_number ? strict_week_number_year :
550
/* Which day of week is 1st Jan of this year */
551
weekday_b= calc_weekday(days, sunday_first_n_first_week_non_iso);
554
Below we are going to sum:
555
1) number of days since year 0 till 1st day of 1st week of this year
556
2) number of days between 1st week and our week
557
3) and position of our day in the week
559
if (sunday_first_n_first_week_non_iso)
561
days+= ((weekday_b == 0) ? 0 : 7) - weekday_b +
562
(week_number - 1) * 7 +
567
days+= ((weekday_b <= 3) ? 0 : 7) - weekday_b +
568
(week_number - 1) * 7 +
572
if (days <= 0 || days > MAX_DAY_NUMBER)
574
get_date_from_daynr(days,&l_time->year,&l_time->month,&l_time->day);
577
if (l_time->month > 12 || l_time->day > 31 || l_time->hour > 23 ||
578
l_time->minute > 59 || l_time->second > 59)
585
if (!my_isspace(&my_charset_latin1,*val))
587
make_truncated_value_warning(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
589
cached_timestamp_type, NullS);
592
} while (++val != val_end);
599
strmake(buff, val_begin, min(length, sizeof(buff)-1));
600
push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_ERROR,
601
ER_WRONG_VALUE_FOR_TYPE, ER(ER_WRONG_VALUE_FOR_TYPE),
602
date_time_type, buff, "str_to_date");
609
Create a formated date/time value in a string.
612
bool make_date_time(DATE_TIME_FORMAT *format, MYSQL_TIME *l_time,
613
timestamp_type type, String *str)
619
const char *ptr, *end;
620
THD *thd= current_thd;
621
MY_LOCALE *locale= thd->variables.lc_time_names;
628
end= (ptr= format->format.str) + format->format.length;
629
for (; ptr != end ; ptr++)
631
if (*ptr != '%' || ptr+1 == end)
639
str->append(locale->month_names->type_names[l_time->month-1],
640
(uint) strlen(locale->month_names->type_names[l_time->month-1]),
641
system_charset_info);
646
str->append(locale->ab_month_names->type_names[l_time->month-1],
647
(uint) strlen(locale->ab_month_names->type_names[l_time->month-1]),
648
system_charset_info);
651
if (type == MYSQL_TIMESTAMP_TIME)
653
weekday= calc_weekday(calc_daynr(l_time->year,l_time->month,
655
str->append(locale->day_names->type_names[weekday],
656
(uint) strlen(locale->day_names->type_names[weekday]),
657
system_charset_info);
660
if (type == MYSQL_TIMESTAMP_TIME)
662
weekday=calc_weekday(calc_daynr(l_time->year,l_time->month,
664
str->append(locale->ab_day_names->type_names[weekday],
665
(uint) strlen(locale->ab_day_names->type_names[weekday]),
666
system_charset_info);
669
if (type == MYSQL_TIMESTAMP_TIME)
671
length= (uint) (int10_to_str(l_time->day, intbuff, 10) - intbuff);
672
str->append_with_prefill(intbuff, length, 1, '0');
673
if (l_time->day >= 10 && l_time->day <= 19)
674
str->append(STRING_WITH_LEN("th"));
677
switch (l_time->day %10) {
679
str->append(STRING_WITH_LEN("st"));
682
str->append(STRING_WITH_LEN("nd"));
685
str->append(STRING_WITH_LEN("rd"));
688
str->append(STRING_WITH_LEN("th"));
694
length= (uint) (int10_to_str(l_time->year, intbuff, 10) - intbuff);
695
str->append_with_prefill(intbuff, length, 4, '0');
698
length= (uint) (int10_to_str(l_time->year%100, intbuff, 10) - intbuff);
699
str->append_with_prefill(intbuff, length, 2, '0');
702
length= (uint) (int10_to_str(l_time->month, intbuff, 10) - intbuff);
703
str->append_with_prefill(intbuff, length, 2, '0');
706
length= (uint) (int10_to_str(l_time->month, intbuff, 10) - intbuff);
707
str->append_with_prefill(intbuff, length, 1, '0');
710
length= (uint) (int10_to_str(l_time->day, intbuff, 10) - intbuff);
711
str->append_with_prefill(intbuff, length, 2, '0');
714
length= (uint) (int10_to_str(l_time->day, intbuff, 10) - intbuff);
715
str->append_with_prefill(intbuff, length, 1, '0');
718
length= (uint) (int10_to_str(l_time->second_part, intbuff, 10) - intbuff);
719
str->append_with_prefill(intbuff, length, 6, '0');
722
length= (uint) (int10_to_str(l_time->hour, intbuff, 10) - intbuff);
723
str->append_with_prefill(intbuff, length, 2, '0');
727
hours_i= (l_time->hour%24 + 11)%12+1;
728
length= (uint) (int10_to_str(hours_i, intbuff, 10) - intbuff);
729
str->append_with_prefill(intbuff, length, 2, '0');
731
case 'i': /* minutes */
732
length= (uint) (int10_to_str(l_time->minute, intbuff, 10) - intbuff);
733
str->append_with_prefill(intbuff, length, 2, '0');
736
if (type == MYSQL_TIMESTAMP_TIME)
738
length= (uint) (int10_to_str(calc_daynr(l_time->year,l_time->month,
740
calc_daynr(l_time->year,1,1) + 1, intbuff, 10) - intbuff);
741
str->append_with_prefill(intbuff, length, 3, '0');
744
length= (uint) (int10_to_str(l_time->hour, intbuff, 10) - intbuff);
745
str->append_with_prefill(intbuff, length, 1, '0');
748
hours_i= (l_time->hour%24 + 11)%12+1;
749
length= (uint) (int10_to_str(hours_i, intbuff, 10) - intbuff);
750
str->append_with_prefill(intbuff, length, 1, '0');
753
hours_i= l_time->hour%24;
754
str->append(hours_i < 12 ? "AM" : "PM",2);
757
length= my_sprintf(intbuff,
759
((l_time->hour % 24) < 12) ?
760
"%02d:%02d:%02d AM" : "%02d:%02d:%02d PM",
761
(l_time->hour+11)%12+1,
764
str->append(intbuff, length);
768
length= (uint) (int10_to_str(l_time->second, intbuff, 10) - intbuff);
769
str->append_with_prefill(intbuff, length, 2, '0');
772
length= my_sprintf(intbuff,
778
str->append(intbuff, length);
784
if (type == MYSQL_TIMESTAMP_TIME)
786
length= (uint) (int10_to_str(calc_week(l_time,
788
WEEK_FIRST_WEEKDAY : WEEK_MONDAY_FIRST,
790
intbuff, 10) - intbuff);
791
str->append_with_prefill(intbuff, length, 2, '0');
798
if (type == MYSQL_TIMESTAMP_TIME)
800
length= (uint) (int10_to_str(calc_week(l_time,
802
(WEEK_YEAR | WEEK_FIRST_WEEKDAY) :
803
(WEEK_YEAR | WEEK_MONDAY_FIRST)),
805
intbuff, 10) - intbuff);
806
str->append_with_prefill(intbuff, length, 2, '0');
813
if (type == MYSQL_TIMESTAMP_TIME)
815
(void) calc_week(l_time,
817
WEEK_YEAR | WEEK_FIRST_WEEKDAY :
818
WEEK_YEAR | WEEK_MONDAY_FIRST),
820
length= (uint) (int10_to_str(year, intbuff, 10) - intbuff);
821
str->append_with_prefill(intbuff, length, 4, '0');
825
if (type == MYSQL_TIMESTAMP_TIME)
827
weekday=calc_weekday(calc_daynr(l_time->year,l_time->month,
829
length= (uint) (int10_to_str(weekday, intbuff, 10) - intbuff);
830
str->append_with_prefill(intbuff, length, 1, '0');
845
Get a array of positive numbers from a string object.
846
Each number is separated by 1 non digit character
847
Return error if there is too many numbers.
848
If there is too few numbers, assume that the numbers are left out
849
from the high end. This allows one to give:
850
DAY_TO_SECOND as "D MM:HH:SS", "MM:HH:SS" "HH:SS" or as seconds.
852
@param length: length of str
853
@param cs: charset of str
854
@param values: array of results
855
@param count: count of elements in result array
856
@param transform_msec: if value is true we suppose
857
that the last part of string value is microseconds
858
and we should transform value to six digit value.
859
For example, '1.1' -> '1.100000'
862
static bool get_interval_info(const char *str,uint length,CHARSET_INFO *cs,
863
uint count, ulonglong *values,
866
const char *end=str+length;
868
while (str != end && !my_isdigit(cs,*str))
871
for (i=0 ; i < count ; i++)
874
const char *start= str;
875
for (value=0; str != end && my_isdigit(cs,*str) ; str++)
876
value= value*LL(10) + (longlong) (*str - '0');
877
if (transform_msec && i == count - 1) // microseconds always last
879
int msec_length= 6 - (int) (str - start);
881
value*= (long)log_10_int[msec_length];
884
while (str != end && !my_isdigit(cs,*str))
886
if (str == end && i != count-1)
889
/* Change values[0...i-1] -> values[0...count-1] */
890
bmove_upp((uchar*) (values+count), (uchar*) (values+i),
892
bzero((uchar*) values, sizeof(*values)*(count-i));
900
longlong Item_func_period_add::val_int()
902
DBUG_ASSERT(fixed == 1);
903
ulong period=(ulong) args[0]->val_int();
904
int months=(int) args[1]->val_int();
906
if ((null_value=args[0]->null_value || args[1]->null_value) ||
908
return 0; /* purecov: inspected */
910
convert_month_to_period((uint) ((int) convert_period_to_month(period)+
915
longlong Item_func_period_diff::val_int()
917
DBUG_ASSERT(fixed == 1);
918
ulong period1=(ulong) args[0]->val_int();
919
ulong period2=(ulong) args[1]->val_int();
921
if ((null_value=args[0]->null_value || args[1]->null_value))
922
return 0; /* purecov: inspected */
923
return (longlong) ((long) convert_period_to_month(period1)-
924
(long) convert_period_to_month(period2));
929
longlong Item_func_to_days::val_int()
931
DBUG_ASSERT(fixed == 1);
933
if (get_arg0_date(<ime, TIME_NO_ZERO_DATE))
935
return (longlong) calc_daynr(ltime.year,ltime.month,ltime.day);
940
Get information about this Item tree monotonicity
943
Item_func_to_days::get_monotonicity_info()
946
Get information about monotonicity of the function represented by this item
950
See enum_monotonicity_info.
953
enum_monotonicity_info Item_func_to_days::get_monotonicity_info() const
955
if (args[0]->type() == Item::FIELD_ITEM)
957
if (args[0]->field_type() == MYSQL_TYPE_DATE)
958
return MONOTONIC_STRICT_INCREASING_NOT_NULL;
959
if (args[0]->field_type() == MYSQL_TYPE_DATETIME)
960
return MONOTONIC_INCREASING_NOT_NULL;
962
return NON_MONOTONIC;
966
longlong Item_func_to_days::val_int_endpoint(bool left_endp, bool *incl_endp)
968
DBUG_ASSERT(fixed == 1);
971
int dummy; /* unused */
972
if (get_arg0_date(<ime, TIME_FUZZY_DATE))
974
/* got NULL, leave the incl_endp intact */
977
res=(longlong) calc_daynr(ltime.year,ltime.month,ltime.day);
978
/* Set to NULL if invalid date, but keep the value */
979
null_value= check_date(<ime,
980
(ltime.year || ltime.month || ltime.day),
981
(TIME_NO_ZERO_IN_DATE | TIME_NO_ZERO_DATE),
986
Even if the evaluation return NULL, the calc_daynr is useful for pruning
988
if (args[0]->field_type() != MYSQL_TYPE_DATE)
993
if (args[0]->field_type() == MYSQL_TYPE_DATE)
995
// TO_DAYS() is strictly monotonic for dates, leave incl_endp intact
1000
Handle the special but practically useful case of datetime values that
1001
point to day bound ("strictly less" comparison stays intact):
1003
col < '2007-09-15 00:00:00' -> TO_DAYS(col) < TO_DAYS('2007-09-15')
1004
col > '2007-09-15 23:59:59' -> TO_DAYS(col) > TO_DAYS('2007-09-15')
1006
which is different from the general case ("strictly less" changes to
1009
col < '2007-09-15 12:34:56' -> TO_DAYS(col) <= TO_DAYS('2007-09-15')
1011
if ((!left_endp && !(ltime.hour || ltime.minute || ltime.second ||
1012
ltime.second_part)) ||
1013
(left_endp && ltime.hour == 23 && ltime.minute == 59 &&
1014
ltime.second == 59))
1023
longlong Item_func_dayofyear::val_int()
1025
DBUG_ASSERT(fixed == 1);
1027
if (get_arg0_date(<ime,TIME_NO_ZERO_DATE))
1029
return (longlong) calc_daynr(ltime.year,ltime.month,ltime.day) -
1030
calc_daynr(ltime.year,1,1) + 1;
1033
longlong Item_func_dayofmonth::val_int()
1035
DBUG_ASSERT(fixed == 1);
1037
(void) get_arg0_date(<ime, TIME_FUZZY_DATE);
1038
return (longlong) ltime.day;
1041
longlong Item_func_month::val_int()
1043
DBUG_ASSERT(fixed == 1);
1045
(void) get_arg0_date(<ime, TIME_FUZZY_DATE);
1046
return (longlong) ltime.month;
1050
void Item_func_monthname::fix_length_and_dec()
1052
THD* thd= current_thd;
1053
CHARSET_INFO *cs= thd->variables.collation_connection;
1054
uint32 repertoire= my_charset_repertoire(cs);
1055
locale= thd->variables.lc_time_names;
1056
collation.set(cs, DERIVATION_COERCIBLE, repertoire);
1058
max_length= locale->max_month_name_length * collation.collation->mbmaxlen;
1063
String* Item_func_monthname::val_str(String* str)
1065
DBUG_ASSERT(fixed == 1);
1066
const char *month_name;
1067
uint month= (uint) val_int();
1070
if (null_value || !month)
1076
month_name= locale->month_names->type_names[month-1];
1077
str->copy(month_name, (uint) strlen(month_name), &my_charset_utf8_bin,
1078
collation.collation, &err);
1084
Returns the quarter of the year.
1087
longlong Item_func_quarter::val_int()
1089
DBUG_ASSERT(fixed == 1);
1091
if (get_arg0_date(<ime, TIME_FUZZY_DATE))
1093
return (longlong) ((ltime.month+2)/3);
1096
longlong Item_func_hour::val_int()
1098
DBUG_ASSERT(fixed == 1);
1100
(void) get_arg0_time(<ime);
1104
longlong Item_func_minute::val_int()
1106
DBUG_ASSERT(fixed == 1);
1108
(void) get_arg0_time(<ime);
1109
return ltime.minute;
1113
Returns the second in time_exp in the range of 0 - 59.
1115
longlong Item_func_second::val_int()
1117
DBUG_ASSERT(fixed == 1);
1119
(void) get_arg0_time(<ime);
1120
return ltime.second;
1124
uint week_mode(uint mode)
1126
uint week_format= (mode & 7);
1127
if (!(week_format & WEEK_MONDAY_FIRST))
1128
week_format^= WEEK_FIRST_WEEKDAY;
1134
The bits in week_format(for calc_week() function) has the following meaning:
1135
WEEK_MONDAY_FIRST (0) If not set Sunday is first day of week
1136
If set Monday is first day of week
1137
WEEK_YEAR (1) If not set Week is in range 0-53
1139
Week 0 is returned for the the last week of the previous year (for
1140
a date at start of january) In this case one can get 53 for the
1141
first week of next year. This flag ensures that the week is
1142
relevant for the given year. Note that this flag is only
1143
releveant if WEEK_JANUARY is not set.
1145
If set Week is in range 1-53.
1147
In this case one may get week 53 for a date in January (when
1148
the week is that last week of previous year) and week 1 for a
1151
WEEK_FIRST_WEEKDAY (2) If not set Weeks are numbered according
1153
If set The week that contains the first
1154
'first-day-of-week' is week 1.
1156
ISO 8601:1988 means that if the week containing January 1 has
1157
four or more days in the new year, then it is week 1;
1158
Otherwise it is the last week of the previous year, and the
1159
next week is week 1.
1163
longlong Item_func_week::val_int()
1165
DBUG_ASSERT(fixed == 1);
1168
if (get_arg0_date(<ime, TIME_NO_ZERO_DATE))
1170
return (longlong) calc_week(<ime,
1171
week_mode((uint) args[1]->val_int()),
1176
longlong Item_func_yearweek::val_int()
1178
DBUG_ASSERT(fixed == 1);
1181
if (get_arg0_date(<ime, TIME_NO_ZERO_DATE))
1183
week= calc_week(<ime,
1184
(week_mode((uint) args[1]->val_int()) | WEEK_YEAR),
1186
return week+year*100;
1190
longlong Item_func_weekday::val_int()
1192
DBUG_ASSERT(fixed == 1);
1195
if (get_arg0_date(<ime, TIME_NO_ZERO_DATE))
1198
return (longlong) calc_weekday(calc_daynr(ltime.year, ltime.month,
1200
odbc_type) + test(odbc_type);
1203
void Item_func_dayname::fix_length_and_dec()
1205
THD* thd= current_thd;
1206
CHARSET_INFO *cs= thd->variables.collation_connection;
1207
uint32 repertoire= my_charset_repertoire(cs);
1208
locale= thd->variables.lc_time_names;
1209
collation.set(cs, DERIVATION_COERCIBLE, repertoire);
1211
max_length= locale->max_day_name_length * collation.collation->mbmaxlen;
1216
String* Item_func_dayname::val_str(String* str)
1218
DBUG_ASSERT(fixed == 1);
1219
uint weekday=(uint) val_int(); // Always Item_func_daynr()
1220
const char *day_name;
1226
day_name= locale->day_names->type_names[weekday];
1227
str->copy(day_name, (uint) strlen(day_name), &my_charset_utf8_bin,
1228
collation.collation, &err);
1233
longlong Item_func_year::val_int()
1235
DBUG_ASSERT(fixed == 1);
1237
(void) get_arg0_date(<ime, TIME_FUZZY_DATE);
1238
return (longlong) ltime.year;
1243
Get information about this Item tree monotonicity
1246
Item_func_year::get_monotonicity_info()
1249
Get information about monotonicity of the function represented by this item
1253
See enum_monotonicity_info.
1256
enum_monotonicity_info Item_func_year::get_monotonicity_info() const
1258
if (args[0]->type() == Item::FIELD_ITEM &&
1259
(args[0]->field_type() == MYSQL_TYPE_DATE ||
1260
args[0]->field_type() == MYSQL_TYPE_DATETIME))
1261
return MONOTONIC_INCREASING;
1262
return NON_MONOTONIC;
1266
longlong Item_func_year::val_int_endpoint(bool left_endp, bool *incl_endp)
1268
DBUG_ASSERT(fixed == 1);
1270
if (get_arg0_date(<ime, TIME_FUZZY_DATE))
1272
/* got NULL, leave the incl_endp intact */
1273
return LONGLONG_MIN;
1277
Handle the special but practically useful case of datetime values that
1278
point to year bound ("strictly less" comparison stays intact) :
1280
col < '2007-01-01 00:00:00' -> YEAR(col) < 2007
1282
which is different from the general case ("strictly less" changes to
1285
col < '2007-09-15 23:00:00' -> YEAR(col) <= 2007
1287
if (!left_endp && ltime.day == 1 && ltime.month == 1 &&
1288
!(ltime.hour || ltime.minute || ltime.second || ltime.second_part))
1296
longlong Item_func_unix_timestamp::val_int()
1301
DBUG_ASSERT(fixed == 1);
1303
return (longlong) current_thd->query_start();
1304
if (args[0]->type() == FIELD_ITEM)
1305
{ // Optimize timestamp field
1306
Field *field=((Item_field*) args[0])->field;
1307
if (field->type() == MYSQL_TYPE_TIMESTAMP)
1308
return ((Field_timestamp*) field)->get_timestamp(&null_value);
1311
if (get_arg0_date(<ime, 0))
1314
We have to set null_value again because get_arg0_date will also set it
1315
to true if we have wrong datetime parameter (and we should return 0 in
1318
null_value= args[0]->null_value;
1322
return (longlong) TIME_to_timestamp(current_thd, <ime, ¬_used);
1326
longlong Item_func_time_to_sec::val_int()
1328
DBUG_ASSERT(fixed == 1);
1331
(void) get_arg0_time(<ime);
1332
seconds=ltime.hour*3600L+ltime.minute*60+ltime.second;
1333
return ltime.neg ? -seconds : seconds;
1338
Convert a string to a interval value.
1340
To make code easy, allow interval objects without separators.
1343
bool get_interval_value(Item *args,interval_type int_type,
1344
String *str_value, INTERVAL *interval)
1347
longlong UNINIT_VAR(value);
1348
const char *UNINIT_VAR(str);
1349
size_t UNINIT_VAR(length);
1350
CHARSET_INFO *cs=str_value->charset();
1352
bzero((char*) interval,sizeof(*interval));
1353
if ((int) int_type <= INTERVAL_MICROSECOND)
1355
value= args->val_int();
1356
if (args->null_value)
1367
if (!(res=args->val_str(str_value)))
1370
/* record negative intervalls in interval->neg */
1372
const char *end=str+res->length();
1373
while (str != end && my_isspace(cs,*str))
1375
if (str != end && *str == '-')
1380
length= (size_t) (end-str); // Set up pointers to new str
1385
interval->year= (ulong) value;
1387
case INTERVAL_QUARTER:
1388
interval->month= (ulong)(value*3);
1390
case INTERVAL_MONTH:
1391
interval->month= (ulong) value;
1394
interval->day= (ulong)(value*7);
1397
interval->day= (ulong) value;
1400
interval->hour= (ulong) value;
1402
case INTERVAL_MICROSECOND:
1403
interval->second_part=value;
1405
case INTERVAL_MINUTE:
1406
interval->minute=value;
1408
case INTERVAL_SECOND:
1409
interval->second=value;
1411
case INTERVAL_YEAR_MONTH: // Allow YEAR-MONTH YYYYYMM
1412
if (get_interval_info(str,length,cs,2,array,0))
1414
interval->year= (ulong) array[0];
1415
interval->month= (ulong) array[1];
1417
case INTERVAL_DAY_HOUR:
1418
if (get_interval_info(str,length,cs,2,array,0))
1420
interval->day= (ulong) array[0];
1421
interval->hour= (ulong) array[1];
1423
case INTERVAL_DAY_MICROSECOND:
1424
if (get_interval_info(str,length,cs,5,array,1))
1426
interval->day= (ulong) array[0];
1427
interval->hour= (ulong) array[1];
1428
interval->minute= array[2];
1429
interval->second= array[3];
1430
interval->second_part= array[4];
1432
case INTERVAL_DAY_MINUTE:
1433
if (get_interval_info(str,length,cs,3,array,0))
1435
interval->day= (ulong) array[0];
1436
interval->hour= (ulong) array[1];
1437
interval->minute= array[2];
1439
case INTERVAL_DAY_SECOND:
1440
if (get_interval_info(str,length,cs,4,array,0))
1442
interval->day= (ulong) array[0];
1443
interval->hour= (ulong) array[1];
1444
interval->minute= array[2];
1445
interval->second= array[3];
1447
case INTERVAL_HOUR_MICROSECOND:
1448
if (get_interval_info(str,length,cs,4,array,1))
1450
interval->hour= (ulong) array[0];
1451
interval->minute= array[1];
1452
interval->second= array[2];
1453
interval->second_part= array[3];
1455
case INTERVAL_HOUR_MINUTE:
1456
if (get_interval_info(str,length,cs,2,array,0))
1458
interval->hour= (ulong) array[0];
1459
interval->minute= array[1];
1461
case INTERVAL_HOUR_SECOND:
1462
if (get_interval_info(str,length,cs,3,array,0))
1464
interval->hour= (ulong) array[0];
1465
interval->minute= array[1];
1466
interval->second= array[2];
1468
case INTERVAL_MINUTE_MICROSECOND:
1469
if (get_interval_info(str,length,cs,3,array,1))
1471
interval->minute= array[0];
1472
interval->second= array[1];
1473
interval->second_part= array[2];
1475
case INTERVAL_MINUTE_SECOND:
1476
if (get_interval_info(str,length,cs,2,array,0))
1478
interval->minute= array[0];
1479
interval->second= array[1];
1481
case INTERVAL_SECOND_MICROSECOND:
1482
if (get_interval_info(str,length,cs,2,array,1))
1484
interval->second= array[0];
1485
interval->second_part= array[1];
1487
case INTERVAL_LAST: /* purecov: begin deadcode */
1489
break; /* purecov: end */
1495
String *Item_date::val_str(String *str)
1497
DBUG_ASSERT(fixed == 1);
1499
if (get_date(<ime, TIME_FUZZY_DATE))
1500
return (String *) 0;
1501
if (str->alloc(MAX_DATE_STRING_REP_LENGTH))
1504
return (String *) 0;
1506
make_date((DATE_TIME_FORMAT *) 0, <ime, str);
1511
longlong Item_date::val_int()
1513
DBUG_ASSERT(fixed == 1);
1515
if (get_date(<ime, TIME_FUZZY_DATE))
1517
return (longlong) (ltime.year*10000L+ltime.month*100+ltime.day);
1521
bool Item_func_from_days::get_date(MYSQL_TIME *ltime, uint fuzzy_date)
1523
longlong value=args[0]->val_int();
1524
if ((null_value=args[0]->null_value))
1526
bzero(ltime, sizeof(MYSQL_TIME));
1527
get_date_from_daynr((long) value, <ime->year, <ime->month, <ime->day);
1528
ltime->time_type= MYSQL_TIMESTAMP_DATE;
1533
void Item_func_curdate::fix_length_and_dec()
1535
collation.set(&my_charset_bin);
1537
max_length=MAX_DATE_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
1539
store_now_in_TIME(<ime);
1541
/* We don't need to set second_part and neg because they already 0 */
1542
ltime.hour= ltime.minute= ltime.second= 0;
1543
ltime.time_type= MYSQL_TIMESTAMP_DATE;
1544
value= (longlong) TIME_to_ulonglong_date(<ime);
1547
String *Item_func_curdate::val_str(String *str)
1549
DBUG_ASSERT(fixed == 1);
1550
if (str->alloc(MAX_DATE_STRING_REP_LENGTH))
1553
return (String *) 0;
1555
make_date((DATE_TIME_FORMAT *) 0, <ime, str);
1560
Converts current time in my_time_t to MYSQL_TIME represenatation for local
1561
time zone. Defines time zone (local) used for whole CURDATE function.
1563
void Item_func_curdate_local::store_now_in_TIME(MYSQL_TIME *now_time)
1565
THD *thd= current_thd;
1566
thd->variables.time_zone->gmt_sec_to_TIME(now_time,
1567
(my_time_t)thd->query_start());
1568
thd->time_zone_used= 1;
1573
Converts current time in my_time_t to MYSQL_TIME represenatation for UTC
1574
time zone. Defines time zone (UTC) used for whole UTC_DATE function.
1576
void Item_func_curdate_utc::store_now_in_TIME(MYSQL_TIME *now_time)
1578
my_tz_UTC->gmt_sec_to_TIME(now_time,
1579
(my_time_t)(current_thd->query_start()));
1581
We are not flagging this query as using time zone, since it uses fixed
1582
UTC-SYSTEM time-zone.
1587
bool Item_func_curdate::get_date(MYSQL_TIME *res,
1588
uint fuzzy_date __attribute__((unused)))
1595
String *Item_func_curtime::val_str(String *str)
1597
DBUG_ASSERT(fixed == 1);
1598
str_value.set(buff, buff_length, &my_charset_bin);
1603
void Item_func_curtime::fix_length_and_dec()
1607
decimals= DATETIME_DEC;
1608
collation.set(&my_charset_bin);
1609
store_now_in_TIME(<ime);
1610
value= TIME_to_ulonglong_time(<ime);
1611
buff_length= (uint) my_time_to_str(<ime, buff);
1612
max_length= buff_length;
1617
Converts current time in my_time_t to MYSQL_TIME represenatation for local
1618
time zone. Defines time zone (local) used for whole CURTIME function.
1620
void Item_func_curtime_local::store_now_in_TIME(MYSQL_TIME *now_time)
1622
THD *thd= current_thd;
1623
thd->variables.time_zone->gmt_sec_to_TIME(now_time,
1624
(my_time_t)thd->query_start());
1625
thd->time_zone_used= 1;
1630
Converts current time in my_time_t to MYSQL_TIME represenatation for UTC
1631
time zone. Defines time zone (UTC) used for whole UTC_TIME function.
1633
void Item_func_curtime_utc::store_now_in_TIME(MYSQL_TIME *now_time)
1635
my_tz_UTC->gmt_sec_to_TIME(now_time,
1636
(my_time_t)(current_thd->query_start()));
1638
We are not flagging this query as using time zone, since it uses fixed
1639
UTC-SYSTEM time-zone.
1644
String *Item_func_now::val_str(String *str)
1646
DBUG_ASSERT(fixed == 1);
1647
str_value.set(buff,buff_length, &my_charset_bin);
1652
void Item_func_now::fix_length_and_dec()
1654
decimals= DATETIME_DEC;
1655
collation.set(&my_charset_bin);
1657
store_now_in_TIME(<ime);
1658
value= (longlong) TIME_to_ulonglong_datetime(<ime);
1660
buff_length= (uint) my_datetime_to_str(<ime, buff);
1661
max_length= buff_length;
1666
Converts current time in my_time_t to MYSQL_TIME represenatation for local
1667
time zone. Defines time zone (local) used for whole NOW function.
1669
void Item_func_now_local::store_now_in_TIME(MYSQL_TIME *now_time)
1671
THD *thd= current_thd;
1672
thd->variables.time_zone->gmt_sec_to_TIME(now_time,
1673
(my_time_t)thd->query_start());
1674
thd->time_zone_used= 1;
1679
Converts current time in my_time_t to MYSQL_TIME represenatation for UTC
1680
time zone. Defines time zone (UTC) used for whole UTC_TIMESTAMP function.
1682
void Item_func_now_utc::store_now_in_TIME(MYSQL_TIME *now_time)
1684
my_tz_UTC->gmt_sec_to_TIME(now_time,
1685
(my_time_t)(current_thd->query_start()));
1687
We are not flagging this query as using time zone, since it uses fixed
1688
UTC-SYSTEM time-zone.
1693
bool Item_func_now::get_date(MYSQL_TIME *res,
1694
uint fuzzy_date __attribute__((unused)))
1701
int Item_func_now::save_in_field(Field *to, bool no_conversions)
1704
return to->store_time(<ime, MYSQL_TIMESTAMP_DATETIME);
1709
Converts current time in my_time_t to MYSQL_TIME represenatation for local
1710
time zone. Defines time zone (local) used for whole SYSDATE function.
1712
void Item_func_sysdate_local::store_now_in_TIME(MYSQL_TIME *now_time)
1714
THD *thd= current_thd;
1715
thd->variables.time_zone->gmt_sec_to_TIME(now_time, (my_time_t) my_time(0));
1716
thd->time_zone_used= 1;
1720
String *Item_func_sysdate_local::val_str(String *str)
1722
DBUG_ASSERT(fixed == 1);
1723
store_now_in_TIME(<ime);
1724
buff_length= (uint) my_datetime_to_str(<ime, buff);
1725
str_value.set(buff, buff_length, &my_charset_bin);
1730
longlong Item_func_sysdate_local::val_int()
1732
DBUG_ASSERT(fixed == 1);
1733
store_now_in_TIME(<ime);
1734
return (longlong) TIME_to_ulonglong_datetime(<ime);
1738
double Item_func_sysdate_local::val_real()
1740
DBUG_ASSERT(fixed == 1);
1741
store_now_in_TIME(<ime);
1742
return ulonglong2double(TIME_to_ulonglong_datetime(<ime));
1746
void Item_func_sysdate_local::fix_length_and_dec()
1749
collation.set(&my_charset_bin);
1750
max_length= MAX_DATETIME_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
1754
bool Item_func_sysdate_local::get_date(MYSQL_TIME *res,
1755
uint fuzzy_date __attribute__((unused)))
1757
store_now_in_TIME(<ime);
1763
int Item_func_sysdate_local::save_in_field(Field *to, bool no_conversions)
1765
store_now_in_TIME(<ime);
1767
to->store_time(<ime, MYSQL_TIMESTAMP_DATETIME);
1772
String *Item_func_sec_to_time::val_str(String *str)
1774
DBUG_ASSERT(fixed == 1);
1776
longlong arg_val= args[0]->val_int();
1778
if ((null_value=args[0]->null_value) ||
1779
str->alloc(MAX_DATE_STRING_REP_LENGTH))
1785
sec_to_time(arg_val, args[0]->unsigned_flag, <ime);
1787
make_time((DATE_TIME_FORMAT *) 0, <ime, str);
1792
longlong Item_func_sec_to_time::val_int()
1794
DBUG_ASSERT(fixed == 1);
1796
longlong arg_val= args[0]->val_int();
1798
if ((null_value=args[0]->null_value))
1801
sec_to_time(arg_val, args[0]->unsigned_flag, <ime);
1803
return (ltime.neg ? -1 : 1) *
1804
((ltime.hour)*10000 + ltime.minute*100 + ltime.second);
1808
void Item_func_date_format::fix_length_and_dec()
1810
THD* thd= current_thd;
1812
Must use this_item() in case it's a local SP variable
1813
(for ->max_length and ->str_value)
1815
Item *arg1= args[1]->this_item();
1818
CHARSET_INFO *cs= thd->variables.collation_connection;
1819
uint32 repertoire= arg1->collation.repertoire;
1820
if (!thd->variables.lc_time_names->is_ascii)
1821
repertoire|= MY_REPERTOIRE_EXTENDED;
1822
collation.set(cs, arg1->collation.derivation, repertoire);
1823
if (arg1->type() == STRING_ITEM)
1824
{ // Optimize the normal case
1826
max_length= format_length(&arg1->str_value) *
1827
collation.collation->mbmaxlen;
1832
max_length=min(arg1->max_length, MAX_BLOB_WIDTH) * 10 *
1833
collation.collation->mbmaxlen;
1834
set_if_smaller(max_length,MAX_BLOB_WIDTH);
1836
maybe_null=1; // If wrong date
1840
bool Item_func_date_format::eq(const Item *item, bool binary_cmp) const
1842
Item_func_date_format *item_func;
1844
if (item->type() != FUNC_ITEM)
1846
if (func_name() != ((Item_func*) item)->func_name())
1850
item_func= (Item_func_date_format*) item;
1851
if (!args[0]->eq(item_func->args[0], binary_cmp))
1854
We must compare format string case sensitive.
1855
This needed because format modifiers with different case,
1856
for example %m and %M, have different meaning.
1858
if (!args[1]->eq(item_func->args[1], 1))
1865
uint Item_func_date_format::format_length(const String *format)
1868
const char *ptr=format->ptr();
1869
const char *end=ptr+format->length();
1871
for (; ptr != end ; ptr++)
1873
if (*ptr != '%' || ptr == end-1)
1878
case 'M': /* month, textual */
1879
case 'W': /* day (of the week), textual */
1880
size += 64; /* large for UTF8 locale data */
1882
case 'D': /* day (of the month), numeric plus english suffix */
1883
case 'Y': /* year, numeric, 4 digits */
1884
case 'x': /* Year, used with 'v' */
1885
case 'X': /* Year, used with 'v, where week starts with Monday' */
1888
case 'a': /* locale's abbreviated weekday name (Sun..Sat) */
1889
case 'b': /* locale's abbreviated month name (Jan.Dec) */
1890
size += 32; /* large for UTF8 locale data */
1892
case 'j': /* day of year (001..366) */
1895
case 'U': /* week (00..52) */
1896
case 'u': /* week (00..52), where week starts with Monday */
1897
case 'V': /* week 1..53 used with 'x' */
1898
case 'v': /* week 1..53 used with 'x', where week starts with Monday */
1899
case 'y': /* year, numeric, 2 digits */
1900
case 'm': /* month, numeric */
1901
case 'd': /* day (of the month), numeric */
1902
case 'h': /* hour (01..12) */
1903
case 'I': /* --||-- */
1904
case 'i': /* minutes, numeric */
1905
case 'l': /* hour ( 1..12) */
1906
case 'p': /* locale's AM or PM */
1907
case 'S': /* second (00..61) */
1908
case 's': /* seconds, numeric */
1909
case 'c': /* month (0..12) */
1910
case 'e': /* day (0..31) */
1913
case 'k': /* hour ( 0..23) */
1914
case 'H': /* hour (00..23; value > 23 OK, padding always 2-digit) */
1915
size += 7; /* docs allow > 23, range depends on sizeof(unsigned int) */
1917
case 'r': /* time, 12-hour (hh:mm:ss [AP]M) */
1920
case 'T': /* time, 24-hour (hh:mm:ss) */
1923
case 'f': /* microseconds */
1926
case 'w': /* day (of the week), numeric */
1938
String *Item_func_date_format::val_str(String *str)
1943
DBUG_ASSERT(fixed == 1);
1945
if (!is_time_format)
1947
if (get_arg0_date(&l_time, TIME_FUZZY_DATE))
1953
if (!(res=args[0]->val_str(str)) ||
1954
(str_to_time_with_warn(res->ptr(), res->length(), &l_time)))
1957
l_time.year=l_time.month=l_time.day=0;
1961
if (!(format = args[1]->val_str(str)) || !format->length())
1967
size=format_length(format);
1969
if (size < MAX_DATE_STRING_REP_LENGTH)
1970
size= MAX_DATE_STRING_REP_LENGTH;
1973
str= &value; // Save result here
1974
if (str->alloc(size))
1977
DATE_TIME_FORMAT date_time_format;
1978
date_time_format.format.str= (char*) format->ptr();
1979
date_time_format.format.length= format->length();
1981
/* Create the result string */
1982
str->set_charset(collation.collation);
1983
if (!make_date_time(&date_time_format, &l_time,
1984
is_time_format ? MYSQL_TIMESTAMP_TIME :
1985
MYSQL_TIMESTAMP_DATE,
1995
void Item_func_from_unixtime::fix_length_and_dec()
1998
collation.set(&my_charset_bin);
1999
decimals= DATETIME_DEC;
2000
max_length=MAX_DATETIME_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
2002
thd->time_zone_used= 1;
2006
String *Item_func_from_unixtime::val_str(String *str)
2008
MYSQL_TIME time_tmp;
2010
DBUG_ASSERT(fixed == 1);
2012
if (get_date(&time_tmp, 0))
2015
if (str->alloc(MAX_DATE_STRING_REP_LENGTH))
2021
make_datetime((DATE_TIME_FORMAT *) 0, &time_tmp, str);
2027
longlong Item_func_from_unixtime::val_int()
2029
MYSQL_TIME time_tmp;
2031
DBUG_ASSERT(fixed == 1);
2033
if (get_date(&time_tmp, 0))
2036
return (longlong) TIME_to_ulonglong_datetime(&time_tmp);
2039
bool Item_func_from_unixtime::get_date(MYSQL_TIME *ltime,
2040
uint fuzzy_date __attribute__((unused)))
2042
ulonglong tmp= (ulonglong)(args[0]->val_int());
2044
"tmp > TIMESTAMP_MAX_VALUE" check also covers case of negative
2045
from_unixtime() argument since tmp is unsigned.
2047
if ((null_value= (args[0]->null_value || tmp > TIMESTAMP_MAX_VALUE)))
2050
thd->variables.time_zone->gmt_sec_to_TIME(ltime, (my_time_t)tmp);
2056
void Item_func_convert_tz::fix_length_and_dec()
2058
collation.set(&my_charset_bin);
2060
max_length= MAX_DATETIME_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
2065
String *Item_func_convert_tz::val_str(String *str)
2067
MYSQL_TIME time_tmp;
2069
if (get_date(&time_tmp, 0))
2072
if (str->alloc(MAX_DATE_STRING_REP_LENGTH))
2078
make_datetime((DATE_TIME_FORMAT *) 0, &time_tmp, str);
2084
longlong Item_func_convert_tz::val_int()
2086
MYSQL_TIME time_tmp;
2088
if (get_date(&time_tmp, 0))
2091
return (longlong)TIME_to_ulonglong_datetime(&time_tmp);
2095
bool Item_func_convert_tz::get_date(MYSQL_TIME *ltime,
2096
uint fuzzy_date __attribute__((unused)))
2098
my_time_t my_time_tmp;
2100
THD *thd= current_thd;
2102
if (!from_tz_cached)
2104
from_tz= my_tz_find(thd, args[1]->val_str(&str));
2105
from_tz_cached= args[1]->const_item();
2110
to_tz= my_tz_find(thd, args[2]->val_str(&str));
2111
to_tz_cached= args[2]->const_item();
2114
if (from_tz==0 || to_tz==0 || get_arg0_date(ltime, TIME_NO_ZERO_DATE))
2122
my_time_tmp= from_tz->TIME_to_gmt_sec(ltime, ¬_used);
2123
/* my_time_tmp is guranteed to be in the allowed range */
2125
to_tz->gmt_sec_to_TIME(ltime, my_time_tmp);
2133
void Item_func_convert_tz::cleanup()
2135
from_tz_cached= to_tz_cached= 0;
2136
Item_date_func::cleanup();
2140
void Item_date_add_interval::fix_length_and_dec()
2142
enum_field_types arg0_field_type;
2144
collation.set(&my_charset_bin);
2146
max_length=MAX_DATETIME_FULL_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
2147
value.alloc(max_length);
2150
The field type for the result of an Item_date function is defined as
2153
- If first arg is a MYSQL_TYPE_DATETIME result is MYSQL_TYPE_DATETIME
2154
- If first arg is a MYSQL_TYPE_DATE and the interval type uses hours,
2155
minutes or seconds then type is MYSQL_TYPE_DATETIME.
2156
- Otherwise the result is MYSQL_TYPE_STRING
2157
(This is because you can't know if the string contains a DATE, MYSQL_TIME or
2160
cached_field_type= MYSQL_TYPE_STRING;
2161
arg0_field_type= args[0]->field_type();
2162
if (arg0_field_type == MYSQL_TYPE_DATETIME ||
2163
arg0_field_type == MYSQL_TYPE_TIMESTAMP)
2164
cached_field_type= MYSQL_TYPE_DATETIME;
2165
else if (arg0_field_type == MYSQL_TYPE_DATE)
2167
if (int_type <= INTERVAL_DAY || int_type == INTERVAL_YEAR_MONTH)
2168
cached_field_type= arg0_field_type;
2170
cached_field_type= MYSQL_TYPE_DATETIME;
2175
/* Here arg[1] is a Item_interval object */
2177
bool Item_date_add_interval::get_date(MYSQL_TIME *ltime, uint fuzzy_date)
2181
if (args[0]->get_date(ltime, TIME_NO_ZERO_DATE) ||
2182
get_interval_value(args[1], int_type, &value, &interval))
2183
return (null_value=1);
2185
if (date_sub_interval)
2186
interval.neg = !interval.neg;
2188
if ((null_value= date_add_interval(ltime, int_type, interval)))
2194
String *Item_date_add_interval::val_str(String *str)
2196
DBUG_ASSERT(fixed == 1);
2198
enum date_time_format_types format;
2200
if (Item_date_add_interval::get_date(<ime, TIME_NO_ZERO_DATE))
2203
if (ltime.time_type == MYSQL_TIMESTAMP_DATE)
2205
else if (ltime.second_part)
2206
format= DATE_TIME_MICROSECOND;
2210
if (!make_datetime(format, <ime, str))
2218
longlong Item_date_add_interval::val_int()
2220
DBUG_ASSERT(fixed == 1);
2223
if (Item_date_add_interval::get_date(<ime, TIME_NO_ZERO_DATE))
2224
return (longlong) 0;
2225
date = (ltime.year*100L + ltime.month)*100L + ltime.day;
2226
return ltime.time_type == MYSQL_TIMESTAMP_DATE ? date :
2227
((date*100L + ltime.hour)*100L+ ltime.minute)*100L + ltime.second;
2232
bool Item_date_add_interval::eq(const Item *item, bool binary_cmp) const
2234
Item_date_add_interval *other= (Item_date_add_interval*) item;
2235
if (!Item_func::eq(item, binary_cmp))
2237
return ((int_type == other->int_type) &&
2238
(date_sub_interval == other->date_sub_interval));
2242
'interval_names' reflects the order of the enumeration interval_type.
2246
static const char *interval_names[]=
2248
"year", "quarter", "month", "week", "day",
2249
"hour", "minute", "second", "microsecond",
2250
"year_month", "day_hour", "day_minute",
2251
"day_second", "hour_minute", "hour_second",
2252
"minute_second", "day_microsecond",
2253
"hour_microsecond", "minute_microsecond",
2254
"second_microsecond"
2257
void Item_date_add_interval::print(String *str, enum_query_type query_type)
2260
args[0]->print(str, query_type);
2261
str->append(date_sub_interval?" - interval ":" + interval ");
2262
args[1]->print(str, query_type);
2264
str->append(interval_names[int_type]);
2268
void Item_extract::print(String *str, enum_query_type query_type)
2270
str->append(STRING_WITH_LEN("extract("));
2271
str->append(interval_names[int_type]);
2272
str->append(STRING_WITH_LEN(" from "));
2273
args[0]->print(str, query_type);
2277
void Item_extract::fix_length_and_dec()
2279
value.alloc(32); // alloc buffer
2281
maybe_null=1; // If wrong date
2283
case INTERVAL_YEAR: max_length=4; date_value=1; break;
2284
case INTERVAL_YEAR_MONTH: max_length=6; date_value=1; break;
2285
case INTERVAL_QUARTER: max_length=2; date_value=1; break;
2286
case INTERVAL_MONTH: max_length=2; date_value=1; break;
2287
case INTERVAL_WEEK: max_length=2; date_value=1; break;
2288
case INTERVAL_DAY: max_length=2; date_value=1; break;
2289
case INTERVAL_DAY_HOUR: max_length=9; date_value=0; break;
2290
case INTERVAL_DAY_MINUTE: max_length=11; date_value=0; break;
2291
case INTERVAL_DAY_SECOND: max_length=13; date_value=0; break;
2292
case INTERVAL_HOUR: max_length=2; date_value=0; break;
2293
case INTERVAL_HOUR_MINUTE: max_length=4; date_value=0; break;
2294
case INTERVAL_HOUR_SECOND: max_length=6; date_value=0; break;
2295
case INTERVAL_MINUTE: max_length=2; date_value=0; break;
2296
case INTERVAL_MINUTE_SECOND: max_length=4; date_value=0; break;
2297
case INTERVAL_SECOND: max_length=2; date_value=0; break;
2298
case INTERVAL_MICROSECOND: max_length=2; date_value=0; break;
2299
case INTERVAL_DAY_MICROSECOND: max_length=20; date_value=0; break;
2300
case INTERVAL_HOUR_MICROSECOND: max_length=13; date_value=0; break;
2301
case INTERVAL_MINUTE_MICROSECOND: max_length=11; date_value=0; break;
2302
case INTERVAL_SECOND_MICROSECOND: max_length=9; date_value=0; break;
2303
case INTERVAL_LAST: DBUG_ASSERT(0); break; /* purecov: deadcode */
2308
longlong Item_extract::val_int()
2310
DBUG_ASSERT(fixed == 1);
2317
if (get_arg0_date(<ime, TIME_FUZZY_DATE))
2323
String *res= args[0]->val_str(&value);
2324
if (!res || str_to_time_with_warn(res->ptr(), res->length(), <ime))
2329
neg= ltime.neg ? -1 : 1;
2333
case INTERVAL_YEAR: return ltime.year;
2334
case INTERVAL_YEAR_MONTH: return ltime.year*100L+ltime.month;
2335
case INTERVAL_QUARTER: return (ltime.month+2)/3;
2336
case INTERVAL_MONTH: return ltime.month;
2339
week_format= current_thd->variables.default_week_format;
2340
return calc_week(<ime, week_mode(week_format), &year);
2342
case INTERVAL_DAY: return ltime.day;
2343
case INTERVAL_DAY_HOUR: return (long) (ltime.day*100L+ltime.hour)*neg;
2344
case INTERVAL_DAY_MINUTE: return (long) (ltime.day*10000L+
2347
case INTERVAL_DAY_SECOND: return ((longlong) ltime.day*1000000L+
2348
(longlong) (ltime.hour*10000L+
2351
case INTERVAL_HOUR: return (long) ltime.hour*neg;
2352
case INTERVAL_HOUR_MINUTE: return (long) (ltime.hour*100+ltime.minute)*neg;
2353
case INTERVAL_HOUR_SECOND: return (long) (ltime.hour*10000+ltime.minute*100+
2355
case INTERVAL_MINUTE: return (long) ltime.minute*neg;
2356
case INTERVAL_MINUTE_SECOND: return (long) (ltime.minute*100+ltime.second)*neg;
2357
case INTERVAL_SECOND: return (long) ltime.second*neg;
2358
case INTERVAL_MICROSECOND: return (long) ltime.second_part*neg;
2359
case INTERVAL_DAY_MICROSECOND: return (((longlong)ltime.day*1000000L +
2360
(longlong)ltime.hour*10000L +
2362
ltime.second)*1000000L +
2363
ltime.second_part)*neg;
2364
case INTERVAL_HOUR_MICROSECOND: return (((longlong)ltime.hour*10000L +
2366
ltime.second)*1000000L +
2367
ltime.second_part)*neg;
2368
case INTERVAL_MINUTE_MICROSECOND: return (((longlong)(ltime.minute*100+
2369
ltime.second))*1000000L+
2370
ltime.second_part)*neg;
2371
case INTERVAL_SECOND_MICROSECOND: return ((longlong)ltime.second*1000000L+
2372
ltime.second_part)*neg;
2373
case INTERVAL_LAST: DBUG_ASSERT(0); break; /* purecov: deadcode */
2375
return 0; // Impossible
2378
bool Item_extract::eq(const Item *item, bool binary_cmp) const
2382
if (item->type() != FUNC_ITEM ||
2383
functype() != ((Item_func*)item)->functype())
2386
Item_extract* ie= (Item_extract*)item;
2387
if (ie->int_type != int_type)
2390
if (!args[0]->eq(ie->args[0], binary_cmp))
2396
bool Item_char_typecast::eq(const Item *item, bool binary_cmp) const
2400
if (item->type() != FUNC_ITEM ||
2401
functype() != ((Item_func*)item)->functype())
2404
Item_char_typecast *cast= (Item_char_typecast*)item;
2405
if (cast_length != cast->cast_length ||
2406
cast_cs != cast->cast_cs)
2409
if (!args[0]->eq(cast->args[0], binary_cmp))
2414
void Item_typecast::print(String *str, enum_query_type query_type)
2416
str->append(STRING_WITH_LEN("cast("));
2417
args[0]->print(str, query_type);
2418
str->append(STRING_WITH_LEN(" as "));
2419
str->append(cast_type());
2424
void Item_char_typecast::print(String *str, enum_query_type query_type)
2426
str->append(STRING_WITH_LEN("cast("));
2427
args[0]->print(str, query_type);
2428
str->append(STRING_WITH_LEN(" as char"));
2429
if (cast_length >= 0)
2433
// my_charset_bin is good enough for numbers
2434
String st(buffer, sizeof(buffer), &my_charset_bin);
2435
st.set((ulonglong)cast_length, &my_charset_bin);
2441
str->append(STRING_WITH_LEN(" charset "));
2442
str->append(cast_cs->csname);
2447
String *Item_char_typecast::val_str(String *str)
2449
DBUG_ASSERT(fixed == 1);
2453
if (!charset_conversion)
2455
if (!(res= args[0]->val_str(str)))
2463
// Convert character set if differ
2465
if (!(res= args[0]->val_str(&tmp_value)) ||
2466
str->copy(res->ptr(), res->length(), from_cs,
2467
cast_cs, &dummy_errors))
2475
res->set_charset(cast_cs);
2478
Cut the tail if cast with length
2479
and the result is longer than cast length, e.g.
2480
CAST('string' AS CHAR(1))
2482
if (cast_length >= 0)
2484
if (res->length() > (length= (uint32) res->charpos(cast_length)))
2485
{ // Safe even if const arg
2487
my_snprintf(char_type, sizeof(char_type), "%s(%lu)",
2488
cast_cs == &my_charset_bin ? "BINARY" : "CHAR",
2491
if (!res->alloced_length())
2492
{ // Don't change const str
2493
str_value= *res; // Not malloced string
2496
push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
2497
ER_TRUNCATED_WRONG_VALUE,
2498
ER(ER_TRUNCATED_WRONG_VALUE), char_type,
2500
res->length((uint) length);
2502
else if (cast_cs == &my_charset_bin && res->length() < (uint) cast_length)
2504
if (res->alloced_length() < (uint) cast_length)
2506
str->alloc(cast_length);
2510
bzero((char*) res->ptr() + res->length(),
2511
(uint) cast_length - res->length());
2512
res->length(cast_length);
2520
void Item_char_typecast::fix_length_and_dec()
2524
We always force character set conversion if cast_cs
2525
is a multi-byte character set. It garantees that the
2526
result of CAST is a well-formed string.
2527
For single-byte character sets we allow just to copy
2528
from the argument. A single-byte character sets string
2529
is always well-formed.
2531
There is a special trick to convert form a number to ucs2.
2532
As numbers have my_charset_bin as their character set,
2533
it wouldn't do conversion to ucs2 without an additional action.
2534
To force conversion, we should pretend to be non-binary.
2535
Let's choose from_cs this way:
2536
- If the argument in a number and cast_cs is ucs2 (i.e. mbminlen > 1),
2537
then from_cs is set to latin1, to perform latin1 -> ucs2 conversion.
2538
- If the argument is a number and cast_cs is ASCII-compatible
2539
(i.e. mbminlen == 1), then from_cs is set to cast_cs,
2540
which allows just to take over the args[0]->val_str() result
2541
and thus avoid unnecessary character set conversion.
2542
- If the argument is not a number, then from_cs is set to
2543
the argument's charset.
2545
Note (TODO): we could use repertoire technique here.
2547
from_cs= (args[0]->result_type() == INT_RESULT ||
2548
args[0]->result_type() == DECIMAL_RESULT ||
2549
args[0]->result_type() == REAL_RESULT) ?
2550
(cast_cs->mbminlen == 1 ? cast_cs : &my_charset_latin1) :
2551
args[0]->collation.collation;
2552
charset_conversion= (cast_cs->mbmaxlen > 1) ||
2553
(!my_charset_same(from_cs, cast_cs) &&
2554
from_cs != &my_charset_bin &&
2555
cast_cs != &my_charset_bin);
2556
collation.set(cast_cs, DERIVATION_IMPLICIT);
2557
char_length= (cast_length >= 0) ? cast_length :
2558
args[0]->max_length /
2559
(cast_cs == &my_charset_bin ? 1 : args[0]->collation.collation->mbmaxlen);
2560
max_length= char_length * cast_cs->mbmaxlen;
2564
String *Item_datetime_typecast::val_str(String *str)
2566
DBUG_ASSERT(fixed == 1);
2569
if (!get_arg0_date(<ime, TIME_FUZZY_DATE) &&
2570
!make_datetime(ltime.second_part ? DATE_TIME_MICROSECOND : DATE_TIME,
2579
longlong Item_datetime_typecast::val_int()
2581
DBUG_ASSERT(fixed == 1);
2583
if (get_arg0_date(<ime,1))
2589
return TIME_to_ulonglong_datetime(<ime);
2593
bool Item_time_typecast::get_time(MYSQL_TIME *ltime)
2595
bool res= get_arg0_time(ltime);
2597
For MYSQL_TIMESTAMP_TIME value we can have non-zero day part,
2598
which we should not lose.
2600
if (ltime->time_type == MYSQL_TIMESTAMP_DATETIME)
2601
ltime->year= ltime->month= ltime->day= 0;
2602
ltime->time_type= MYSQL_TIMESTAMP_TIME;
2607
longlong Item_time_typecast::val_int()
2610
if (get_time(<ime))
2615
return ltime.hour * 10000L + ltime.minute * 100 + ltime.second;
2618
String *Item_time_typecast::val_str(String *str)
2620
DBUG_ASSERT(fixed == 1);
2623
if (!get_arg0_time(<ime) &&
2624
!make_datetime(ltime.second_part ? TIME_MICROSECOND : TIME_ONLY,
2633
bool Item_date_typecast::get_date(MYSQL_TIME *ltime, uint fuzzy_date)
2635
bool res= get_arg0_date(ltime, TIME_FUZZY_DATE);
2636
ltime->hour= ltime->minute= ltime->second= ltime->second_part= 0;
2637
ltime->time_type= MYSQL_TIMESTAMP_DATE;
2642
bool Item_date_typecast::get_time(MYSQL_TIME *ltime)
2644
bzero((char *)ltime, sizeof(MYSQL_TIME));
2645
return args[0]->null_value;
2649
String *Item_date_typecast::val_str(String *str)
2651
DBUG_ASSERT(fixed == 1);
2654
if (!get_arg0_date(<ime, TIME_FUZZY_DATE) &&
2655
!str->alloc(MAX_DATE_STRING_REP_LENGTH))
2657
make_date((DATE_TIME_FORMAT *) 0, <ime, str);
2665
longlong Item_date_typecast::val_int()
2667
DBUG_ASSERT(fixed == 1);
2669
if ((null_value= args[0]->get_date(<ime, TIME_FUZZY_DATE)))
2671
return (longlong) (ltime.year * 10000L + ltime.month * 100 + ltime.day);
2675
MAKEDATE(a,b) is a date function that creates a date value
2676
from a year and day value.
2679
As arguments are integers, we can't know if the year is a 2 digit or 4 digit year.
2680
In this case we treat all years < 100 as 2 digit years. Ie, this is not safe
2681
for dates between 0000-01-01 and 0099-12-31
2684
String *Item_func_makedate::val_str(String *str)
2686
DBUG_ASSERT(fixed == 1);
2688
long daynr= (long) args[1]->val_int();
2689
long year= (long) args[0]->val_int();
2692
if (args[0]->null_value || args[1]->null_value ||
2693
year < 0 || daynr <= 0)
2697
year= year_2000_handling(year);
2699
days= calc_daynr(year,1,1) + daynr - 1;
2700
/* Day number from year 0 to 9999-12-31 */
2701
if (days >= 0 && days <= MAX_DAY_NUMBER)
2704
get_date_from_daynr(days,&l_time.year,&l_time.month,&l_time.day);
2705
if (str->alloc(MAX_DATE_STRING_REP_LENGTH))
2707
make_date((DATE_TIME_FORMAT *) 0, &l_time, str);
2718
MAKEDATE(a,b) is a date function that creates a date value
2719
from a year and day value.
2722
As arguments are integers, we can't know if the year is a 2 digit or 4 digit year.
2723
In this case we treat all years < 100 as 2 digit years. Ie, this is not safe
2724
for dates between 0000-01-01 and 0099-12-31
2727
longlong Item_func_makedate::val_int()
2729
DBUG_ASSERT(fixed == 1);
2731
long daynr= (long) args[1]->val_int();
2732
long year= (long) args[0]->val_int();
2735
if (args[0]->null_value || args[1]->null_value ||
2736
year < 0 || daynr <= 0)
2740
year= year_2000_handling(year);
2742
days= calc_daynr(year,1,1) + daynr - 1;
2743
/* Day number from year 0 to 9999-12-31 */
2744
if (days >= 0 && days < MAX_DAY_NUMBER)
2747
get_date_from_daynr(days,&l_time.year,&l_time.month,&l_time.day);
2748
return (longlong) (l_time.year * 10000L + l_time.month * 100 + l_time.day);
2757
void Item_func_add_time::fix_length_and_dec()
2759
enum_field_types arg0_field_type;
2761
max_length=MAX_DATETIME_FULL_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
2765
The field type for the result of an Item_func_add_time function is defined
2768
- If first arg is a MYSQL_TYPE_DATETIME or MYSQL_TYPE_TIMESTAMP
2769
result is MYSQL_TYPE_DATETIME
2770
- If first arg is a MYSQL_TYPE_TIME result is MYSQL_TYPE_TIME
2771
- Otherwise the result is MYSQL_TYPE_STRING
2774
cached_field_type= MYSQL_TYPE_STRING;
2775
arg0_field_type= args[0]->field_type();
2776
if (arg0_field_type == MYSQL_TYPE_DATE ||
2777
arg0_field_type == MYSQL_TYPE_DATETIME ||
2778
arg0_field_type == MYSQL_TYPE_TIMESTAMP)
2779
cached_field_type= MYSQL_TYPE_DATETIME;
2780
else if (arg0_field_type == MYSQL_TYPE_TIME)
2781
cached_field_type= MYSQL_TYPE_TIME;
2785
ADDTIME(t,a) and SUBTIME(t,a) are time functions that calculate a
2788
t: time_or_datetime_expression
2791
Result: Time value or datetime value
2794
String *Item_func_add_time::val_str(String *str)
2796
DBUG_ASSERT(fixed == 1);
2797
MYSQL_TIME l_time1, l_time2, l_time3;
2799
long days, microseconds;
2804
if (is_date) // TIMESTAMP function
2806
if (get_arg0_date(&l_time1, TIME_FUZZY_DATE) ||
2807
args[1]->get_time(&l_time2) ||
2808
l_time1.time_type == MYSQL_TIMESTAMP_TIME ||
2809
l_time2.time_type != MYSQL_TIMESTAMP_TIME)
2812
else // ADDTIME function
2814
if (args[0]->get_time(&l_time1) ||
2815
args[1]->get_time(&l_time2) ||
2816
l_time2.time_type == MYSQL_TIMESTAMP_DATETIME)
2818
is_time= (l_time1.time_type == MYSQL_TIMESTAMP_TIME);
2820
if (l_time1.neg != l_time2.neg)
2823
bzero((char *)&l_time3, sizeof(l_time3));
2825
l_time3.neg= calc_time_diff(&l_time1, &l_time2, -l_sign,
2826
&seconds, µseconds);
2829
If first argument was negative and diff between arguments
2830
is non-zero we need to swap sign to get proper result.
2832
if (l_time1.neg && (seconds || microseconds))
2833
l_time3.neg= 1-l_time3.neg; // Swap sign of result
2835
if (!is_time && l_time3.neg)
2838
days= (long)(seconds/86400L);
2840
calc_time_from_sec(&l_time3, (long)(seconds%86400L), microseconds);
2844
get_date_from_daynr(days,&l_time3.year,&l_time3.month,&l_time3.day);
2846
!make_datetime(l_time1.second_part || l_time2.second_part ?
2847
DATE_TIME_MICROSECOND : DATE_TIME,
2853
l_time3.hour+= days*24;
2854
if (!make_datetime_with_warn(l_time1.second_part || l_time2.second_part ?
2855
TIME_MICROSECOND : TIME_ONLY,
2865
void Item_func_add_time::print(String *str, enum_query_type query_type)
2869
DBUG_ASSERT(sign > 0);
2870
str->append(STRING_WITH_LEN("timestamp("));
2875
str->append(STRING_WITH_LEN("addtime("));
2877
str->append(STRING_WITH_LEN("subtime("));
2879
args[0]->print(str, query_type);
2881
args[1]->print(str, query_type);
2887
TIMEDIFF(t,s) is a time function that calculates the
2888
time value between a start and end time.
2890
t and s: time_or_datetime_expression
2894
String *Item_func_timediff::val_str(String *str)
2896
DBUG_ASSERT(fixed == 1);
2900
MYSQL_TIME l_time1 ,l_time2, l_time3;
2903
if (args[0]->get_time(&l_time1) ||
2904
args[1]->get_time(&l_time2) ||
2905
l_time1.time_type != l_time2.time_type)
2908
if (l_time1.neg != l_time2.neg)
2911
bzero((char *)&l_time3, sizeof(l_time3));
2913
l_time3.neg= calc_time_diff(&l_time1, &l_time2, l_sign,
2914
&seconds, µseconds);
2917
For MYSQL_TIMESTAMP_TIME only:
2918
If first argument was negative and diff between arguments
2919
is non-zero we need to swap sign to get proper result.
2921
if (l_time1.neg && (seconds || microseconds))
2922
l_time3.neg= 1-l_time3.neg; // Swap sign of result
2924
calc_time_from_sec(&l_time3, (long) seconds, microseconds);
2926
if (!make_datetime_with_warn(l_time1.second_part || l_time2.second_part ?
2927
TIME_MICROSECOND : TIME_ONLY,
2937
MAKETIME(h,m,s) is a time function that calculates a time value
2938
from the total number of hours, minutes, and seconds.
2942
String *Item_func_maketime::val_str(String *str)
2944
DBUG_ASSERT(fixed == 1);
2948
longlong hour= args[0]->val_int();
2949
longlong minute= args[1]->val_int();
2950
longlong second= args[2]->val_int();
2952
if ((null_value=(args[0]->null_value ||
2953
args[1]->null_value ||
2954
args[2]->null_value ||
2955
minute < 0 || minute > 59 ||
2956
second < 0 || second > 59 ||
2957
str->alloc(MAX_DATE_STRING_REP_LENGTH))))
2960
bzero((char *)<ime, sizeof(ltime));
2963
/* Check for integer overflows */
2966
if (args[0]->unsigned_flag)
2971
if (-hour > UINT_MAX || hour > UINT_MAX)
2976
ltime.hour= (uint) ((hour < 0 ? -hour : hour));
2977
ltime.minute= (uint) minute;
2978
ltime.second= (uint) second;
2982
ltime.hour= TIME_MAX_HOUR;
2983
ltime.minute= TIME_MAX_MINUTE;
2984
ltime.second= TIME_MAX_SECOND;
2986
char *ptr= longlong10_to_str(hour, buf, args[0]->unsigned_flag ? 10 : -10);
2987
int len = (int)(ptr - buf) +
2988
my_sprintf(ptr, (ptr, ":%02u:%02u", (uint)minute, (uint)second));
2989
make_truncated_value_warning(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
2990
buf, len, MYSQL_TIMESTAMP_TIME,
2994
if (make_time_with_warn((DATE_TIME_FORMAT *) 0, <ime, str))
3004
MICROSECOND(a) is a function ( extraction) that extracts the microseconds
3007
a: Datetime or time value
3011
longlong Item_func_microsecond::val_int()
3013
DBUG_ASSERT(fixed == 1);
3015
if (!get_arg0_time(<ime))
3016
return ltime.second_part;
3021
longlong Item_func_timestamp_diff::val_int()
3023
MYSQL_TIME ltime1, ltime2;
3030
if (args[0]->get_date(<ime1, TIME_NO_ZERO_DATE) ||
3031
args[1]->get_date(<ime2, TIME_NO_ZERO_DATE))
3034
if (calc_time_diff(<ime2,<ime1, 1,
3035
&seconds, µseconds))
3038
if (int_type == INTERVAL_YEAR ||
3039
int_type == INTERVAL_QUARTER ||
3040
int_type == INTERVAL_MONTH)
3042
uint year_beg, year_end, month_beg, month_end, day_beg, day_end;
3044
uint second_beg, second_end, microsecond_beg, microsecond_end;
3048
year_beg= ltime2.year;
3049
year_end= ltime1.year;
3050
month_beg= ltime2.month;
3051
month_end= ltime1.month;
3052
day_beg= ltime2.day;
3053
day_end= ltime1.day;
3054
second_beg= ltime2.hour * 3600 + ltime2.minute * 60 + ltime2.second;
3055
second_end= ltime1.hour * 3600 + ltime1.minute * 60 + ltime1.second;
3056
microsecond_beg= ltime2.second_part;
3057
microsecond_end= ltime1.second_part;
3061
year_beg= ltime1.year;
3062
year_end= ltime2.year;
3063
month_beg= ltime1.month;
3064
month_end= ltime2.month;
3065
day_beg= ltime1.day;
3066
day_end= ltime2.day;
3067
second_beg= ltime1.hour * 3600 + ltime1.minute * 60 + ltime1.second;
3068
second_end= ltime2.hour * 3600 + ltime2.minute * 60 + ltime2.second;
3069
microsecond_beg= ltime1.second_part;
3070
microsecond_end= ltime2.second_part;
3074
years= year_end - year_beg;
3075
if (month_end < month_beg || (month_end == month_beg && day_end < day_beg))
3080
if (month_end < month_beg || (month_end == month_beg && day_end < day_beg))
3081
months+= 12 - (month_beg - month_end);
3083
months+= (month_end - month_beg);
3085
if (day_end < day_beg)
3087
else if ((day_end == day_beg) &&
3088
((second_end < second_beg) ||
3089
(second_end == second_beg && microsecond_end < microsecond_beg)))
3095
return months/12*neg;
3096
case INTERVAL_QUARTER:
3097
return months/3*neg;
3098
case INTERVAL_MONTH:
3101
return seconds/86400L/7L*neg;
3103
return seconds/86400L*neg;
3105
return seconds/3600L*neg;
3106
case INTERVAL_MINUTE:
3107
return seconds/60L*neg;
3108
case INTERVAL_SECOND:
3110
case INTERVAL_MICROSECOND:
3112
In MySQL difference between any two valid datetime values
3113
in microseconds fits into longlong.
3115
return (seconds*1000000L+microseconds)*neg;
3126
void Item_func_timestamp_diff::print(String *str, enum_query_type query_type)
3128
str->append(func_name());
3133
str->append(STRING_WITH_LEN("YEAR"));
3135
case INTERVAL_QUARTER:
3136
str->append(STRING_WITH_LEN("QUARTER"));
3138
case INTERVAL_MONTH:
3139
str->append(STRING_WITH_LEN("MONTH"));
3142
str->append(STRING_WITH_LEN("WEEK"));
3145
str->append(STRING_WITH_LEN("DAY"));
3148
str->append(STRING_WITH_LEN("HOUR"));
3150
case INTERVAL_MINUTE:
3151
str->append(STRING_WITH_LEN("MINUTE"));
3153
case INTERVAL_SECOND:
3154
str->append(STRING_WITH_LEN("SECOND"));
3156
case INTERVAL_MICROSECOND:
3157
str->append(STRING_WITH_LEN("SECOND_FRAC"));
3163
for (uint i=0 ; i < 2 ; i++)
3166
args[i]->print(str, query_type);
3172
String *Item_func_get_format::val_str(String *str)
3174
DBUG_ASSERT(fixed == 1);
3175
const char *format_name;
3176
KNOWN_DATE_TIME_FORMAT *format;
3177
String *val= args[0]->val_str(str);
3180
if ((null_value= args[0]->null_value))
3183
val_len= val->length();
3184
for (format= &known_date_time_formats[0];
3185
(format_name= format->format_name);
3188
uint format_name_len;
3189
format_name_len= (uint) strlen(format_name);
3190
if (val_len == format_name_len &&
3191
!my_strnncoll(&my_charset_latin1,
3192
(const uchar *) val->ptr(), val_len,
3193
(const uchar *) format_name, val_len))
3195
const char *format_str= get_date_time_format_str(format, type);
3196
str->set(format_str, (uint) strlen(format_str), &my_charset_bin);
3206
void Item_func_get_format::print(String *str, enum_query_type query_type)
3208
str->append(func_name());
3212
case MYSQL_TIMESTAMP_DATE:
3213
str->append(STRING_WITH_LEN("DATE, "));
3215
case MYSQL_TIMESTAMP_DATETIME:
3216
str->append(STRING_WITH_LEN("DATETIME, "));
3218
case MYSQL_TIMESTAMP_TIME:
3219
str->append(STRING_WITH_LEN("TIME, "));
3224
args[0]->print(str, query_type);
3230
Get type of datetime value (DATE/TIME/...) which will be produced
3231
according to format string.
3233
@param format format string
3234
@param length length of format string
3237
We don't process day format's characters('D', 'd', 'e') because day
3238
may be a member of all date/time types.
3241
Format specifiers supported by this function should be in sync with
3242
specifiers supported by extract_date_time() function.
3245
One of date_time_format_types values:
3246
- DATE_TIME_MICROSECOND
3253
static date_time_format_types
3254
get_date_time_result_type(const char *format, uint length)
3256
const char *time_part_frms= "HISThiklrs";
3257
const char *date_part_frms= "MVUXYWabcjmvuxyw";
3258
bool date_part_used= 0, time_part_used= 0, frac_second_used= 0;
3260
const char *val= format;
3261
const char *end= format + length;
3263
for (; val != end && val != end; val++)
3265
if (*val == '%' && val+1 != end)
3269
frac_second_used= time_part_used= 1;
3270
else if (!time_part_used && strchr(time_part_frms, *val))
3272
else if (!date_part_used && strchr(date_part_frms, *val))
3274
if (date_part_used && frac_second_used)
3277
frac_second_used implies time_part_used, and thus we already
3278
have all types of date-time components and can end our search.
3280
return DATE_TIME_MICROSECOND;
3285
/* We don't have all three types of date-time components */
3286
if (frac_second_used)
3287
return TIME_MICROSECOND;
3298
void Item_func_str_to_date::fix_length_and_dec()
3302
cached_field_type= MYSQL_TYPE_DATETIME;
3303
max_length= MAX_DATETIME_FULL_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
3304
cached_timestamp_type= MYSQL_TIMESTAMP_NONE;
3305
if ((const_item= args[1]->const_item()))
3307
char format_buff[64];
3308
String format_str(format_buff, sizeof(format_buff), &my_charset_bin);
3309
String *format= args[1]->val_str(&format_str);
3310
if (!args[1]->null_value)
3312
cached_format_type= get_date_time_result_type(format->ptr(),
3314
switch (cached_format_type) {
3316
cached_timestamp_type= MYSQL_TIMESTAMP_DATE;
3317
cached_field_type= MYSQL_TYPE_DATE;
3318
max_length= MAX_DATE_WIDTH * MY_CHARSET_BIN_MB_MAXLEN;
3321
case TIME_MICROSECOND:
3322
cached_timestamp_type= MYSQL_TIMESTAMP_TIME;
3323
cached_field_type= MYSQL_TYPE_TIME;
3324
max_length= MAX_TIME_WIDTH * MY_CHARSET_BIN_MB_MAXLEN;
3327
cached_timestamp_type= MYSQL_TIMESTAMP_DATETIME;
3328
cached_field_type= MYSQL_TYPE_DATETIME;
3336
bool Item_func_str_to_date::get_date(MYSQL_TIME *ltime, uint fuzzy_date)
3338
DATE_TIME_FORMAT date_time_format;
3339
char val_buff[64], format_buff[64];
3340
String val_string(val_buff, sizeof(val_buff), &my_charset_bin), *val;
3341
String format_str(format_buff, sizeof(format_buff), &my_charset_bin), *format;
3343
val= args[0]->val_str(&val_string);
3344
format= args[1]->val_str(&format_str);
3345
if (args[0]->null_value || args[1]->null_value)
3349
bzero((char*) ltime, sizeof(*ltime));
3350
date_time_format.format.str= (char*) format->ptr();
3351
date_time_format.format.length= format->length();
3352
if (extract_date_time(&date_time_format, val->ptr(), val->length(),
3353
ltime, cached_timestamp_type, 0, "datetime") ||
3354
((fuzzy_date & TIME_NO_ZERO_DATE) &&
3355
(ltime->year == 0 || ltime->month == 0 || ltime->day == 0)))
3357
if (cached_timestamp_type == MYSQL_TIMESTAMP_TIME && ltime->day)
3360
Day part for time type can be nonzero value and so
3361
we should add hours from day part to hour part to
3362
keep valid time value.
3364
ltime->hour+= ltime->day*24;
3370
return (null_value=1);
3374
String *Item_func_str_to_date::val_str(String *str)
3376
DBUG_ASSERT(fixed == 1);
3379
if (Item_func_str_to_date::get_date(<ime, TIME_FUZZY_DATE))
3382
if (!make_datetime((const_item ? cached_format_type :
3383
(ltime.second_part ? DATE_TIME_MICROSECOND : DATE_TIME)),
3390
bool Item_func_last_day::get_date(MYSQL_TIME *ltime, uint fuzzy_date)
3392
if (get_arg0_date(ltime, fuzzy_date & ~TIME_FUZZY_DATE) ||
3393
(ltime->month == 0))
3399
uint month_idx= ltime->month-1;
3400
ltime->day= days_in_month[month_idx];
3401
if ( month_idx == 1 && calc_days_in_year(ltime->year) == 366)
3403
ltime->hour= ltime->minute= ltime->second= 0;
3404
ltime->second_part= 0;
3405
ltime->time_type= MYSQL_TIMESTAMP_DATE;