Time and Date Function
Last Updated：2021-04-13
Palo supports two types of time: DATE and DATETIME.
- The format of DATE type is: "2020-10-10"
- The format of DATETIME is: "2020-10-10 11:10:06"
Palo supports the following date and time functions:
1.add_months
2.adddate
3.convert_tz
4.curdate,current_date
5.current_timestamp
6.curtime,current_time
7.date_add
8.date_sub
9.date_format
10.datediff
11.day,dayofmonth
12.dayname
13.dayofweek
14.dayofyear
15.days_add
16.days_diff
17.days_sub
18.extract
19.from_days
20.from_unixtime
21.unix_timestamp
22.utc_timestamp
23.hour
24.hours_add
25.hours_diff
26.hours_sub
27.localtime,localtimestamp
28.microseconds_add
29.microseconds_sub
30.minute
31.minutes_add
32.minutes_diff
33.minutes_sub
34.month
35.monthname
36.months_add
37.months_diff
38.months_sub
39.now
40.second
41.seconds_add
42.seconds_diff
43.seconds_sub
44.subdate
45.str_to_date
46.timediff
47.to_date
48.to_days
49.weeks_add
50.weekofyear
51.weeks_diff
52.weeks_sub
53.quarter
54.year
55.year_floor
56.years_add
57.years_diff
58.years_sub
ADD_MONTHS
Description
add_months(timestamp date, int months)
add_months(timestamp date, bigint months)- Function: return a new date consisting of specified date and months, same as months_add()
- Return type: timestamp type
Example
If this day of this month does not exist in the target month, the result will be the last day of that month;if months in the parameter is negative, then calculate the previous month.
mysql> select now(), add_months(now(), 2);
+---------------------+---------------------+
| now() | add_months(now(), 2)|
+---------------------+---------------------+
| 2016-05-31 10:47:00 | 2016-07-31 10:47:00 |
+---------------------+---------------------+
1 row in set (0.01 sec)
mysql> select now(), add_months(now(), 1);
+---------------------+---------------------+
| now() | add_months(now(), 1)|
+---------------------+---------------------+
| 2016-05-31 10:47:14 | 2016-06-30 10:47:14 |
+---------------------+---------------------+
1 row in set (0.01 sec)
mysql> select now(), add_months(now(), -1);
+---------------------+----------------------+
| now() | add_months(now(), -1)|
+---------------------+----------------------+
| 2016-05-31 10:47:31 | 2016-04-30 10:47:31 |
+---------------------+----------------------+
1 row in set (0.01 sec)Keywords
add_monthsADDDATE
Description
adddate(timestamp startdate, int days)
adddate(timestamp startdate, bigint days)- Function: add specified number of days to startdate
- Return type: timestamp type
Example
mysql> select adddate(date_column, 10) from big_table limit 1;
+-------------------------------+
| adddate(date_column, 10) |
+-------------------------------+
| 2014-01-11 00:00:00 |
+-------------------------------+Keywords
adddateCONVERT_TZ
Description
convert_tz(timestamp date, string from, string to)- Function: convert the time zone of the specified time
- Return type: timestamp type
Example
mysql> select convert_tz('2020-12-20 12:00:00','+00:00','+10:00');
+-------------------------------------------------------+
| convert_tz('2020-12-20 12:00:00', '+00:00', '+10:00') |
+-------------------------------------------------------+
| 2020-12-20 22:00:00 |
+-------------------------------------------------------+
1 row in set (0.09 sec)Keywords
convert_tz CURDATE,CURRENT_DATE
Description
curdate()
current_date()- Function: get the current date
- Return type: timestamp type
Example
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2020-12-25 |
+------------+
1 row in set (0.03 sec)
mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2020-12-25 |
+----------------+
1 row in set (0.02 sec)Keywords
curdate,current_dateCURRENT_TIMESTAMP
Description
current_timestamp()- Function: it has the same function as the now () function and gets the current time
- Return type: timestamp type
Example
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2020-12-25 14:13:10 |
+---------------------+
1 row in set (0.03 sec)Keywords
CURRENT_TIMESTAMPCURTIME,CURRENT_TIME
Description
curtime()
current_time()- Function: get the current time, excluding the date field
- Return type: timestamp type
Example
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 14:24:07 |
+-----------+
1 row in set (0.02 sec)
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 14:24:22 |
+----------------+
1 row in set (0.08 sec)Keywords
curtime,current_timeDATE_ADD
Description
date_add(timestamp startdate, int days)- Function: add the specified number of days to the TIMESTAMP value, the first parameter can be a string, and if the string conforms to the format of TIMESTAMP data type, the string will be automatically converted to TIMESTAMP type. The second parameter is the time interval.
- Return type: timestamp type
Example
mysql> select date_add('2020-12-25',20);
+-------------------------------------+
| date_add('2020-12-25 00:00:00', 20) |
+-------------------------------------+
| 2021-01-14 00:00:00 |
+-------------------------------------+
1 row in set (0.03 sec)Keywords
date_addDATE_SUB
Description
date_sub(timestamp startdate, int days)- Function: minus the specified number of days to the TIMESTAMP value. The first parameter can be a string, and if the string conforms to the format of TIMESTAMP data type, the string will be automatically converted to TIMESTAMP type. The second parameter is the time interval.
- Return type: timestamp type
Example
mysql> select date_sub('2020-12-25',20);
+-------------------------------------+
| date_sub('2020-12-25 00:00:00', 20) |
+-------------------------------------+
| 2020-12-05 00:00:00 |
+-------------------------------------+
1 row in set (0.02 sec)Keywords
date_subDATE_FORMAT
Description
date_format(timestamp day, string fmt)- Function: convert the date type into a string by the format type, currently supporting the string of max 128 bytes. If the return length exceeds 128, then return NULL.
- Return type: string type
-
The meaning of format is as follows:
%a Abbreviated weekday name (Sun..Sat) %b Abbreviated month name (Jan..Dec) %c Month, numeric (0..12) %D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …) %d Day of the month, numeric (00..31) %e Day of the month, numeric (0..31) %f Microseconds (000000..999999) %H Hour (00..23) %h Hour (01..12) %I Hour (01..12) %i Minutes, numeric (00..59) %j Day of year (001..366) %k Hour (0..23) %l Hour (1..12) %M Month name (January..December) %m Month, numeric (00..12) %p AM or PM %r Time, 12-hour (hh:mm:ss followed by AM or PM) %S Seconds (00..59) %s Seconds (00..59) %T Time, 24-hour (hh:mm:ss) %U Week (00..53), where Sunday is the first day of the week; WEEK() mode 0 %u Week (00..53), where Monday is the first day of the week; WEEK() mode 1 %V Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X %v Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x %W Weekday name (Sunday..Saturday) %w Day of the week (0=Sunday..6=Saturday) %X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V %x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v %Y Year, numeric, four digits %y Year, numeric (two digits) %% A literal “%” character %x x, for any “x” not listed above
Example
mysql> select date_format('2009-10-04 22:23:00', '%W %M %Y');
+------------------------------------------------+
| date_format('2009-10-04 22:23:00', '%W %M %Y') |
+------------------------------------------------+
| Sunday October 2009 |
+------------------------------------------------+
1 row in set (0.01 sec)
mysql> select date_format('2007-10-04 22:23:00', '%H:%i:%s');
+------------------------------------------------+
| date_format('2007-10-04 22:23:00', '%H:%i:%s') |
+------------------------------------------------+
| 22:23:00 |
+------------------------------------------------+
1 row in set (0.01 sec)
mysql> select date_format('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');
+------------------------------------------------------------+
| date_format('1900-10-04 22:23:00', '%D %y %a %d %m %b %j') |
+------------------------------------------------------------+
| 4th 00 Thu 04 10 Oct 277 |
+------------------------------------------------------------+
1 row in set (0.03 sec)
mysql> select date_format('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
+------------------------------------------------------------+
| date_format('1997-10-04 22:23:00', '%H %k %I %r %T %S %w') |
+------------------------------------------------------------+
| 22 22 10 10:23:00 PM 22:23:00 00 6 |
+------------------------------------------------------------+
1 row in set (0.00 sec)Keywords
date_formatDATEDIFF
Description
datediff(string enddate, string startdate)- Function: return the difference in the number of days between two dates
- Return type: int type
Example
mysql> select datediff('2020-12-25','2019-11-20');
+--------------------------------------------------------+
| datediff('2020-12-25 00:00:00', '2019-11-20 00:00:00') |
+--------------------------------------------------------+
| 401 |
+--------------------------------------------------------+
1 row in set (0.03 sec)Keywords
datediffDAY,DAYOFMONTH
Description
day(string date)
dayofmonth(string date)- Function: return the day field in a date
- Return type: int type
Example
mysql> select day('2020-12-25');
+----------------------------+
| day('2020-12-25 00:00:00') |
+----------------------------+
| 25 |
+----------------------------+
1 row in set (0.02 sec)
mysql> select dayofmonth('2020-12-25');
+-----------------------------------+
| dayofmonth('2020-12-25 00:00:00') |
+-----------------------------------+
| 25 |
+-----------------------------------+
1 row in set (0.07 sec)Keywords
day,dayofmonthDAYNAME
Description
dayname(timestamp date)- Function: return the specified day of the week (English)
- Return type: string type
Example
mysql> select dayname('2020-12-25');
+--------------------------------+
| dayname('2020-12-25 00:00:00') |
+--------------------------------+
| Friday |
+--------------------------------+
1 row in set (0.04 sec)Keywords
dayname DAYOFWEEK
Description
dayofweek(timestamp date)-
Function: return the specified day of the week (number)
Notes: 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday.
- Return type: int type
Example
mysql> select dayofweek('2020-12-25');
+----------------------------------+
| dayofweek('2020-12-25 00:00:00') |
+----------------------------------+
| 6 |
+----------------------------------+
1 row in set (0.07 sec)Keywords
dayofweekDAYOFYEAR
Description
dayofyear(timestamp date)- Function: return the specified day of the current year
- Return type: int type
Example
mysql> select dayofyear('2020-12-25');
+----------------------------------+
| dayofyear('2020-12-25 00:00:00') |
+----------------------------------+
| 360 |
+----------------------------------+
1 row in set (0.02 sec)Keywords
dayofyearDAYS_ADD
Description
days_add(timestamp startdate, int days)
days_add(timestamp startdate, bigint days)- Function: add the specified number of days to startdate, which is similar to the date_add function, except that the parameter of this function is TIMESTAMP type instead of string type.
- Return type: timestamp type
Example
mysql> select days_add('2020-12-25',10);
+-------------------------------------+
| days_add('2020-12-25 00:00:00', 10) |
+-------------------------------------+
| 2021-01-04 00:00:00 |
+-------------------------------------+
1 row in set (0.02 sec)Keywords
days_addDAYS_DIFF
Description
days_diff(timestamp enddate, timestamp startdate)- Function: the difference between the start time and the end time
- Return type: int type
Example
mysql> select days_diff('2020-12-25','2020-10-1');
+---------------------------------------------------------+
| days_diff('2020-12-25 00:00:00', '2020-10-01 00:00:00') |
+---------------------------------------------------------+
| 85 |
+---------------------------------------------------------+
1 row in set (0.06 sec)Keywords
days_diffDAYS_SUB
Description
days_sub(timestamp startdate, int days)
days_sub(timestamp startdate, bigint days)- Function: subtract specified number of days from startdate; which is similar to the date_sub function, except that the parameter of this function is TIMESTAMP type instead of string type.
- Return type: timestamp type
Example
mysql> select days_sub('2020-12-25',20);
+-------------------------------------+
| days_sub('2020-12-25 00:00:00', 20) |
+-------------------------------------+
| 2020-12-05 00:00:00 |
+-------------------------------------+
1 row in set (0.03 sec)Keywords
days_subEXTRACT
Description
extract(unit FROM timestamp)- Function: extract the value of timestamp in a specified unit. The unit can be year, month, day, hour, minute or second
- Return type: int type
Example
mysql> select now() as right_now,
-> extract(year from now()) as this_year,
-> extract(month from now()) as this_month;
+---------------------+-----------+------------+
| right_now | this_year | this_month |
+---------------------+-----------+------------+
| 2020-12-25 18:27:13 | 2020 | 12 |
+---------------------+-----------+------------+
1 row in set (0.02 sec)
mysql> select now() as right_now,
-> extract(day from now()) as this_day,
-> extract(hour from now()) as this_hour;
+---------------------+----------+-----------+
| right_now | this_day | this_hour |
+---------------------+----------+-----------+
| 2020-12-25 18:28:26 | 25 | 18 |
+---------------------+----------+-----------+
1 row in set (0.02 sec)Keywords
extract
FROM_DAYS
Description
from_days(int days)- Function: return the date from 0000-00-00 for the specified number of days backward
- Return type: timestamp
Example
mysql> select from_days(10000);
+------------------+
| from_days(10000) |
+------------------+
| 0027-05-19 |
+------------------+
1 row in set (0.09 sec)Keywords
from_daysFROM_UNIXTIME
Description
from_unixtime(bigint unixtime,[ string format])- Function: convert unix time (seconds elapsed since January 1, 1970) to a date type in the corresponding format
- Return type: string type
- Instructions for use: the current date format is case-sensitive, users should especially distinguish between lowercase m (representing minutes) and uppercase M (representing months). The complete pattern of the date string is "yyyy-MM-dd HH: mm: Ss.SSSSSS ",or it can only contain some of the fields.
Example
mysql> select from_unixtime(100000);
+-----------------------+
| from_unixtime(100000) |
+-----------------------+
| 1970-01-02 11:46:40 |
+-----------------------+
1 row in set (0.01 sec)
mysql> select from_unixtime(100000, 'yyyy-MM-dd');
+-------------------------------------+
| from_unixtime(100000, 'yyyy-MM-dd') |
+-------------------------------------+
| 1970-01-02 |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> select from_unixtime(1392394861, 'yyyy-MM-dd');
+-----------------------------------------+
| from_unixtime(1392394861, 'yyyy-MM-dd') |
+-----------------------------------------+
| 2014-02-15 |
+-----------------------------------------+
1 row in set (0.00 sec)unix_timestamp() and from_unixtime() are often used in combination to convert the timestamp type into a string in a specified format.
mysql> select from_unixtime(unix_timestamp(now()), 'yyyy-MM-dd');
+--------------------------------------------------+
| from_unixtime(unix_timestamp(now()), '%Y-%m-%d') |
+--------------------------------------------------+
| 2020-12-25 |
+--------------------------------------------------+
1 row in set (0.02 sec)
mysql> select from_unixtime(unix_timestamp(now()), '%Y %D %M');
+--------------------------------------------------+
| from_unixtime(unix_timestamp(now()), '%Y %D %M') |
+--------------------------------------------------+
| 2020 25th December |
+--------------------------------------------------+
1 row in set (0.03 sec)UNIX_TIMESTAMP
Description
unix_timestamp()
unix_timestamp(string datetime)
unix_timestamp(string datetime, string format)
unix_timestamp(timestamp datetime)- Function: return the timestamp of the current time (relative to the number of seconds of January 1, 1970) or convert it from a specified date and time to a timestamp. The timestamp returned is a timestamp relative to Greenwich Mean Time Zone.
- Return type: bigint type
Example
mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
| 1608896139 |
+------------------+
1 row in set (0.03 sec)Keywords
unix_timestampUTC_TIMESTAMP
Description
utc_timestamp()- Function: return the time in the current Greenwich Mean Time Zone
- Return type: timestamp
Example
mysql> select utc_timestamp();
+---------------------+
| utc_timestamp() |
+---------------------+
| 2020-12-25 11:39:30 |
+---------------------+
1 row in set (0.02 sec)Keywords
utc_timestampHOUR
Description
hour(string date)- Function: return the hour field of the date expressed by a string
- Return type: int type
Example
mysql> select hour('2020-12-25 23:46');
+--------------------------+
| hour('2020-12-25 23:46') |
+--------------------------+
| 23 |
+--------------------------+
1 row in set (0.02 sec)Keywords
hourHOURS_ADD
Description
hours_add(timestamp date, int hours)
hours_add(timestamp date, bigint hours)- Function: return the time of specified date plus the number of hours
- Return type: timestamp
Example
mysql> select hours_add('2020-12-25 18:00', 24);
+-----------------------------------+
| hours_add('2020-12-25 18:00', 24) |
+-----------------------------------+
| 2020-12-26 18:00:00 |
+-----------------------------------+
1 row in set (0.06 sec)
mysql> select hours_add(now(), 24);
+----------------------+
| hours_add(now(), 24) |
+----------------------+
| 2020-12-26 19:45:40 |
+----------------------+
1 row in set (0.02 sec)Keywords
hours_addHOURS_DIFF
Description
hours_diff(timestamp enddate, timestamp startdate)- Function: the difference between the start time and the end time, in days
- Return type: int type
Example
mysql> select hours_diff('2020-12-26 08:00','2020-12-25 20:00');
+----------------------------------------------------+
| hours_diff('2020-12-26 08:00', '2020-12-25 20:00') |
+----------------------------------------------------+
| 12 |
+----------------------------------------------------+
1 row in set (0.04 sec)Keywords
hours_diffHOURS_SUB
Description
hours_sub(timestamp date, int hours)
hours_sub(timestamp date, bigint hours)- Function: return the time of specified date subtracting plus the number of hours
- Return type: timestamp
Example
mysql> select hours_sub('2020-12-25 20:00',12);
+-----------------------------------+
| hours_sub('2020-12-25 20:00', 12) |
+-----------------------------------+
| 2020-12-25 08:00:00 |
+-----------------------------------+
1 row in set (0.05 sec)Keywords
hours_subLOCALTIME,LOCALTIMESTAMP
Description
localtime ()
localtimestamp()- Function: it has the same function as the now () function and gets the current time
- Return type: timestamp type
Example
mysql> select localtime();
+---------------------+
| localtime() |
+---------------------+
| 2020-12-25 19:52:56 |
+---------------------+
1 row in set (0.04 sec)
mysql> select localtimestamp();
+---------------------+
| localtimestamp() |
+---------------------+
| 2020-12-25 19:53:10 |
+---------------------+
1 row in set (0.02 sec)Keywords
localtime,localtimestampMICROSECONDS_ADD
Description
microseconds_add(timestamp date, int microseconds)
microseconds_add(timestamp date, bigint microseconds)- Function: return the time of specified date plus the number of microseconds
- Return type: timestamp
Example
mysql> select microseconds_add('2020-12-25',1000000);
+--------------------------------------------------+
| microseconds_add('2020-12-25 00:00:00', 1000000) |
+--------------------------------------------------+
| 2020-12-25 00:00:01 |
+--------------------------------------------------+
1 row in set (0.02 sec)Keywords
microseconds_addMICROSECONDS_SUB
Description
microseconds_sub(timestamp date, int microseconds)
microseconds_sub(timestamp date, bigint microseconds)- Function: return the time of specified date subtracting the number of microseconds
- Return type: timestamp
Example
mysql> select microseconds_sub('2020-12-25',1000000);
+--------------------------------------------------+
| microseconds_sub('2020-12-25 00:00:00', 1000000) |
+--------------------------------------------------+
| 2020-12-24 23:59:59 |
+--------------------------------------------------+
1 row in set (0.03 sec)Keywords
microseconds_subMINUTE
Description
minute(string date)- Function: return the minutes field of the date expressed by a string
- Return type: int type
Example
mysql> select minute('2020-12-25 20:25:35');
+-------------------------------+
| minute('2020-12-25 20:25:35') |
+-------------------------------+
| 25 |
+-------------------------------+
1 row in set (0.04 sec)Keywords
minuteMINUTES_ADD
Description
minutes_add(timestamp date, int minutes)
minutes_add(timestamp date, bigint minutes)- Function: return the time of specified date plus the number of minutes
- Return type: timestamp
Example
mysql> select minutes_add('2020-12-25 20:00:00',25);
+----------------------------------------+
| minutes_add('2020-12-25 20:00:00', 25) |
+----------------------------------------+
| 2020-12-25 20:25:00 |
+----------------------------------------+
1 row in set (0.02 sec)Keywords
minutes_add
MINUTES_DIFF
Description
minutes_diff(timestamp enddate, timestamp startdate)- Function: the difference between the start time and the end time, in minutes
- Return type: int type
Example
mysql> select minutes_diff('2020-12-25 22:00:00','2020-12-25 21:00:00');
+------------------------------------------------------------+
| minutes_diff('2020-12-25 22:00:00', '2020-12-25 21:00:00') |
+------------------------------------------------------------+
| 60 |
+------------------------------------------------------------+
1 row in set (0.02 sec)Keywords
minutes_diffMINUTES_SUB
Description
minutes_sub(timestamp date, int minutes)
minutes_sub(timestamp date, bigint minutes)- Function: return the time of specified date subtracting the number of minutes
- Return type: timestamp
Example
mysql> select minutes_sub('2020-12-25 20:00:00',25);
+----------------------------------------+
| minutes_sub('2020-12-25 20:00:00', 25) |
+----------------------------------------+
| 2020-12-25 19:35:00 |
+----------------------------------------+
1 row in set (0.02 sec)Keywords
minutes_subMONTH
Description
month(string date)- Function: return the month field (number) of the date expressed by the string
- Return type: int type
Example
mysql> select month('2020-12-25');
+------------------------------+
| month('2020-12-25 00:00:00') |
+------------------------------+
| 12 |
+------------------------------+
1 row in set (0.02 sec)Keywords
monthMONTHNAME
Description
monthname(string date)- Function: return the month field (English) of the date expressed by the string
- Return type: string type
Example
mysql> select monthname('2020-12-25');
+----------------------------------+
| monthname('2020-12-25 00:00:00') |
+----------------------------------+
| December |
+----------------------------------+
1 row in set (0.02 sec)Keywords
MONTHS_ADD
Description
months_add(timestamp date, int months)
months_add(timestamp date, bigint months)- Function: return the time of specified date plus the number of months
- Return type: timestamp
Example
mysql> select months_add('2020-12-25',2);
+--------------------------------------+
| months_add('2020-12-25 00:00:00', 2) |
+--------------------------------------+
| 2021-02-25 00:00:00 |
+--------------------------------------+
1 row in set (0.03 sec)Keywords
months_addMONTHS_DIFF
Description
months_diff(timestamp enddate, timestamp startdate)- Function: the difference between the start time and the end time, in months
- Return type: int type
Example
mysql> select months_diff('2020-12-25','2020-05-25');
+-----------------------------------------------------------+
| months_diff('2020-12-25 00:00:00', '2020-05-25 00:00:00') |
+-----------------------------------------------------------+
| 7 |
+-----------------------------------------------------------+
1 row in set (0.02 sec)Keywords
months_diffMONTHS_SUB
Description
months_sub(timestamp date, int months)
months_sub(timestamp date, bigint months)- Function: return the time of specified date subtracting the number of months
- Return type: timestamp
Example
mysql> select months_sub('2020-12-25',2);
+--------------------------------------+
| months_sub('2020-12-25 00:00:00', 2) |
+--------------------------------------+
| 2020-10-25 00:00:00 |
+--------------------------------------+
1 row in set (0.02 sec)Keywords
months_subNOW
Description
now()- Function: return the current date and time (the time zone of the East Eighth District)
- Return type: timestamp
Example
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-12-25 20:36:03 |
+---------------------+
1 row in set (0.20 sec)Keywords
nowSECOND
Description
second(string date)- Function: return the seconds field of the date expressed by a string
- Return type: int type
Example
mysql> select now() as right_now,
-> second(now());
+---------------------+---------------+
| right_now | second(now()) |
+---------------------+---------------+
| 2020-12-25 20:44:25 | 25 |
+---------------------+---------------+
1 row in set (0.03 sec)Keywords
secondSECONDS_ADD
Description
seconds_add(timestamp date, int seconds)
seconds_add(timestamp date, bigint seconds)- Function: return the time of specified date plus the number of seconds
- Return type: timestamp
Example
mysql> select now() as right_now,
-> seconds_add(now(),20);
+---------------------+------------------------+
| right_now | seconds_add(now(), 20) |
+---------------------+------------------------+
| 2020-12-25 20:45:07 | 2020-12-25 20:45:27 |
+---------------------+------------------------+
1 row in set (0.02 sec)Keywords
seconds_addSECONDS_DIFF
Description
seconds_diff(timestamp enddate, timestamp startdate)- Function: the difference between the start time and the end time, in seconds
- Return type: int type
Example
mysql> select seconds_diff('2020-12-25 08:00:00','2020-12-25 07:00:00');
+------------------------------------------------------------+
| seconds_diff('2020-12-25 08:00:00', '2020-12-25 07:00:00') |
+------------------------------------------------------------+
| 3600 |
+------------------------------------------------------------+
1 row in set (0.05 sec)Keywords
seconds_diffSECONDS_SUB
Description
seconds_sub(timestamp date, int seconds)
seconds_sub(timestamp date, bigint seconds)- Function: subtract the number of days from the startdate.
- Return type: timestamp
Example
mysql> select now() as right_now,
-> seconds_sub(now(),20);
+---------------------+------------------------+
| right_now | seconds_sub(now(), 20) |
+---------------------+------------------------+
| 2020-12-25 20:46:10 | 2020-12-25 20:45:50 |
+---------------------+------------------------+
1 row in set (0.03 sec)Keywords
seconds_subSUBDATE
Description
subdate(timestamp startdate, int days)
subdate(timestamp startdate, bigint days)- Function: similar to the date_sub () function, but the first parameter of this function is the exact TIMESTAMP, not a string that can be converted into TIMESTAMP type.
- Return type: timestamp
Example
mysql> select subdate('2020-12-25',10);
+------------------------------------+
| subdate('2020-12-25 00:00:00', 10) |
+------------------------------------+
| 2020-12-15 00:00:00 |
+------------------------------------+
1 row in set (0.02 sec)Keywords
subdateSTR_TO_DATE
Description
str_to_date(string str, string format)- Function: translate str into timestamp type in the way specified by format, and return NULL if the conversion result is incorrect. The format supported is consistent with date_format.
- Return type: timestamp
Example
mysql> select str_to_date('20201225 1130','%Y%m%d %h%i');
+---------------------------------------------+
| str_to_date('20201225 1130', '%Y%m%d %h%i') |
+---------------------------------------------+
| 2020-12-25 11:30:00 |
+---------------------------------------------+
1 row in set (0.03 sec)Keywords
str_to_dateTIMEDIFF
Description
timediff(string enddate, string startdate)- Function: return the timestamp difference between two times
- Return type: int type
Example
mysql> select timediff('20201225','20201224');
+--------------------------------------------------------+
| timediff('2020-12-25 00:00:00', '2020-12-24 00:00:00') |
+--------------------------------------------------------+
| 24:00:00 |
+--------------------------------------------------------+
1 row in set (0.02 sec)Keywords
timediffTO_DATE
Description
to_date(timestamp)- Function: return the date domain of timestamp
- Return type: string type
Example
mysql> select now() as right_now,
-> concat('The date today is ',to_date(now()),'.') as date_announcement;
+---------------------+-------------------------------+
| right_now | date_announcement |
+---------------------+-------------------------------+
| 2020-12-25 21:08:02 | The date today is 2020-12-25. |
+---------------------+-------------------------------+
1 row in set (0.03 sec)Keywords
to_dateTO_DAYS
Description
to_days(timestamp date)- Function: return the number of days from 0000-00-00 to the specified date
- Return type: int type
Example
mysql> select to_days('0000-12-25');
+-----------------------+
| to_days('0000-12-25') |
+-----------------------+
| 359 |
+-----------------------+
1 row in set (0.03 sec)Keywords
to_daysWEEK_ADD
Description
weeks_add(timestamp date, int weeks)
weeks_add(timestamp date, bigint weeks)- Function: return the time of specified date plus the number of weeks
- Return type: timestamp
Example
mysql> select weeks_add('2020-12-25',3);
+-------------------------------------+
| weeks_add('2020-12-25 00:00:00', 3) |
+-------------------------------------+
| 2021-01-15 00:00:00 |
+-------------------------------------+
1 row in set (0.03 sec)Keywords
weeks_addWEEKOFYEAR
Description
weekofyear(timestamp date)- Function: get the week of the year
- Return type: int
Example
mysql> select weekofyear('2020-12-25');
+-----------------------------------+
| weekofyear('2020-12-25 00:00:00') |
+-----------------------------------+
| 52 |
+-----------------------------------+
1 row in set (0.03 sec)Keywords
weekofyearWEEK_DIFF
Description
weeks_diff(timestamp enddate, timestamp startdate)- Function: the difference between the start time and the end time, in weeks
- Return type: int type
Example
mysql> select weeks_diff('2020-12-25','2020-10-25');
+----------------------------------------------------------+
| weeks_diff('2020-12-25 00:00:00', '2020-10-25 00:00:00') |
+----------------------------------------------------------+
| 8 |
+----------------------------------------------------------+
1 row in set (0.02 sec)Keywords
weeks_diffWEEK_SUD
Description
weeks_sub(timestamp date, int weeks)
weeks_sub(timestamp date, bigint weeks)- Function: return the time of specified date subtracting the number of weeks
- Return type: timestamp
Example
mysql> select weeks_sub('2020-12-25',3);
+-------------------------------------+
| weeks_sub('2020-12-25 00:00:00', 3) |
+-------------------------------------+
| 2020-12-04 00:00:00 |
+-------------------------------------+
1 row in set (0.28 sec)Keywords
weeks_subQUARTER
Description
quarter(timestamp date)- Function: return the quarter to which the specified date belongs
- Return type: int
Example
mysql> select quarter('2020-12-25');
+--------------------------------+
| quarter('2020-12-25 00:00:00') |
+--------------------------------+
| 4 |
+--------------------------------+
1 row in set (0.02 sec)Keywords
quarterYEAR
Description
year(string date)- Function: return the year field of the date expressed by a string
- Return type: int type
Example
mysql> select year('2020-12-25');
+-----------------------------+
| year('2020-12-25 00:00:00') |
+-----------------------------+
| 2020 |
+-----------------------------+
1 row in set (0.02 sec)Keywords
yearYEAR_FLOOR
Description
year_floor(timestamp date)- Function: round down the specified time, keep the field level to year
- Return type: timestamp
Example
mysql> select year_floor('2020-12-25');
+-----------------------------------+
| year_floor('2020-12-25 00:00:00') |
+-----------------------------------+
| 2020-01-01 00:00:00 |
+-----------------------------------+
1 row in set (0.04 sec)Keywords
year_floorYEARS_ADD
Description
years_add(timestamp date, int years)
years_add(timestamp date, bigint years)- Function: return the time of specified date plus the number of years
- Return type: timestamp
Example
mysql> select years_add('2020-12-25',10);
+--------------------------------------+
| years_add('2020-12-25 00:00:00', 10) |
+--------------------------------------+
| 2030-12-25 00:00:00 |
+--------------------------------------+
1 row in set (0.03 sec)Keywords
years_addYEAR_DIFF
Description
years_diff(timestamp enddate, timestamp startdate)- Function: the difference between the start time and the end time, in years
- Return type: int type
Example
mysql> select years_diff('2020-12-25','2011-04-30');
+----------------------------------------------------------+
| years_diff('2020-12-25 00:00:00', '2011-04-30 00:00:00') |
+----------------------------------------------------------+
| 9 |
+----------------------------------------------------------+
1 row in set (0.02 sec)Keywords
years_diffYEAR_SUB
Description
years_sub(timestamp date, int years)
years_sub(timestamp date, bigint years)- Function: return the specified date subtracting the number of years
- Return type: timestamp
Example
mysql> select years_sub('2020-12-25',10);
+--------------------------------------+
| years_sub('2020-12-25 00:00:00', 10) |
+--------------------------------------+
| 2010-12-25 00:00:00 |
+--------------------------------------+
1 row in set (0.03 sec)Keywords
years_sub