MySQL DATE_SUB() Function
DATE_SUB(date, INTERVAL value interval)
Parameter Description
date Required. The date to be modified
value Required. The value of the time/date interval to subtract. Both positive and negative values are allowed
interval Required. The type of interval to subtract. Can be one of the following values:
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
------------------
SELECT DATE_SUB("2021-06-15 11:31:55", INTERVAL 30 MINUTE);
SELECT DATE_SUB("2021-06-15", INTERVAL -6 MONTH);
SELECT DATE_SUB("2021-06-15 08:39:14", INTERVAL 5 HOUR);
9059868766
===========
The DAY() function returns the day of the month for a given date (a number from 1 to 31).
SELECT DAY(CURDATE());
==========
The DAYNAME() function returns the weekday name for a given date.
=========
The DAYOFMONTH() function returns the day of the month for a given date (a number from 1 to 31).
SELECT DAYOFMONTH("2021-06-15 09:34:21");
=======
The DAYOFWEEK() function returns the weekday index for a given date (a number from 1 to 7).
Note: 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday.
SELECT DAYOFWEEK("2021-06-15 09:34:21");
===========
The DAYOFYEAR() function returns the day of the year for a given date (a number from 1 to 366).
SELECT DAYOFYEAR(CURDATE());
-----------
The EXTRACT() function extracts a part from a given date.
EXTRACT(part FROM date)
SELECT EXTRACT(WEEK FROM "2021-06-05");
SELECT EXTRACT(MINUTE FROM "2021-06-05 09:34:21");
SELECT EXTRACT(YEAR_MONTH FROM "2021-06-05 09:34:21");
==================
Definition and Usage
The FROM_DAYS() function returns a date from a numeric datevalue.
The FROM_DAYS() function is to be used only with dates within the Gregorian calendar.
Note: This function is the opposite of the TO_DAYS() function.
SELECT FROM_DAYS(780500);
==============
The HOUR() function returns the hour part for a given date (from 0 to 838).
SELECT HOUR("2017-06-20 09:34:00");
SELECT HOUR("838:59:59");
===========
The LAST_DAY() function extracts the last day of the month for a given date.
SELECT LAST_DAY("2017-02-10 09:34:00");
============
The LOCALTIME() function returns the current date and time.
Note: The date and time is returned as "YYYY-MM-DD HH-MM-SS" (string) or as YYYYMMDDHHMMSS.uuuuuu (numeric).
SELECT LOCALTIME() + 1;
===========
The LOCALTIMESTAMP() function returns the current date and time.
Note: The date and time is returned as "YYYY-MM-DD HH-MM-SS" (string) or as YYYYMMDDHHMMSS.uuuuuu (numeric).
SELECT LOCALTIMESTAMP() + 1;
-----------
SELECT MAKEDATE(2021, 3);
The MAKEDATE() function creates and returns a date based on a year and a number of days value.
SELECT MAKEDATE(2017, 366);
---------------
The MAKETIME() function creates and returns a time based on an hour, minute, and second value.
MAKETIME(hour, minute, second)
SELECT MAKETIME(21, 59, 59);
----------------
The MICROSECOND() function returns the microsecond part of a time/datetime (from 0 to 999999)
SELECT MICROSECOND("2017-06-20 09:34:00.000023");
-----------
The MINUTE() function returns the minute part of a time/datetime (from 0 to 59).
SELECT MINUTE("23:59:59");
-------------
The MONTH() function returns the month part for a given date (a number from 1 to 12).
SELECT MONTH("2021-06-05 09:34:21");
--------------
The MONTHNAME() function returns the name of the month for a given date.
SELECT MONTHNAME("2021-06-05");
--------------
The NOW() function returns the current date and time.
Note: The date and time is returned as "YYYY-MM-DD HH-MM-SS" (string) or as YYYYMMDDHHMMSS.uuuuuu (numeric).
-------------------
practice
MariaDB [vlrinst]> SELECT DATE_SUB("2021-06-15 11:31:55", INTERVAL 30 MINUTE);
+-----------------------------------------------------+
| DATE_SUB("2021-06-15 11:31:55", INTERVAL 30 MINUTE) |
+-----------------------------------------------------+
| 2021-06-15 11:01:55 |
+-----------------------------------------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT DATE_SUB("2021-06-15 11:31:55", INTERVAL 30 Hour);
+---------------------------------------------------+
| DATE_SUB("2021-06-15 11:31:55", INTERVAL 30 Hour) |
+---------------------------------------------------+
| 2021-06-14 05:31:55 |
+---------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT DATE_SUB("2021-06-15 11:31:55", INTERVAL -30 Hour);
+----------------------------------------------------+
| DATE_SUB("2021-06-15 11:31:55", INTERVAL -30 Hour) |
+----------------------------------------------------+
| 2021-06-16 17:31:55 |
+----------------------------------------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT day("2021-06-15 11:31:55");
+----------------------------+
| day("2021-06-15 11:31:55") |
+----------------------------+
| 15 |
+----------------------------+
1 row in set (0.032 sec)
MariaDB [vlrinst]> SELECT day(cur_date());
ERROR 1305 (42000): FUNCTION vlrinst.cur_date does not exist
MariaDB [vlrinst]> SELECT day(curdate());
+----------------+
| day(curdate()) |
+----------------+
| 5 |
+----------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT dayname(curdate());
+--------------------+
| dayname(curdate()) |
+--------------------+
| Saturday |
+--------------------+
1 row in set (0.017 sec)
MariaDB [vlrinst]>
MariaDB [vlrinst]>
MariaDB [vlrinst]>
MariaDB [vlrinst]>
MariaDB [vlrinst]>
MariaDB [vlrinst]>
MariaDB [vlrinst]>
MariaDB [vlrinst]>
MariaDB [vlrinst]>
MariaDB [vlrinst]>
MariaDB [vlrinst]>
MariaDB [vlrinst]>
MariaDB [vlrinst]> SELECT DAYOFMONTH(curdate());
+-----------------------+
| DAYOFMONTH(curdate()) |
+-----------------------+
| 5 |
+-----------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT DAYOFWEEK(curdate());
+----------------------+
| DAYOFWEEK(curdate()) |
+----------------------+
| 7 |
+----------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT DAYOFYEAR(curdate());
+----------------------+
| DAYOFYEAR(curdate()) |
+----------------------+
| 156 |
+----------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT EXTRACT(YEAR_MONTH FROM "2021-06-05 09:34:21");
+------------------------------------------------+
| EXTRACT(YEAR_MONTH FROM "2021-06-05 09:34:21") |
+------------------------------------------------+
| 202106 |
+------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT FROM_DAYS(780500);
+-------------------+
| FROM_DAYS(780500) |
+-------------------+
| 2136-12-08 |
+-------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT FROM_DAYS(1);
+--------------+
| FROM_DAYS(1) |
+--------------+
| 0000-00-00 |
+--------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT FROM_DAYS(2);
+--------------+
| FROM_DAYS(2) |
+--------------+
| 0000-00-00 |
+--------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT FROM_DAYS(1000);
+-----------------+
| FROM_DAYS(1000) |
+-----------------+
| 0002-09-27 |
+-----------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT FROM_DAYS(11000);
+------------------+
| FROM_DAYS(11000) |
+------------------+
| 0030-02-12 |
+------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT FROM_DAYS(111000);
+-------------------+
| FROM_DAYS(111000) |
+-------------------+
| 0303-11-29 |
+-------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT FROM_DAYS(1111000);
+--------------------+
| FROM_DAYS(1111000) |
+--------------------+
| 3041-10-25 |
+--------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT FROM_DAYS(211000);
+-------------------+
| FROM_DAYS(211000) |
+-------------------+
| 0577-09-12 |
+-------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT FROM_DAYS(311000);
+-------------------+
| FROM_DAYS(311000) |
+-------------------+
| 0851-06-28 |
+-------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT HOUR("838:59:59");
+-------------------+
| HOUR("838:59:59") |
+-------------------+
| 838 |
+-------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT HOUR(curdate());
+-----------------+
| HOUR(curdate()) |
+-----------------+
| 0 |
+-----------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT HOUR("2017-06-20 09:34:00");
+-----------------------------+
| HOUR("2017-06-20 09:34:00") |
+-----------------------------+
| 9 |
+-----------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT HOUR("2017-06-20 09:34:00");
+-----------------------------+
| HOUR("2017-06-20 09:34:00") |
+-----------------------------+
| 9 |
+-----------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> select curdate();
+------------+
| curdate() |
+------------+
| 2021-06-05 |
+------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> select now();
+---------------------+
| now() |
+---------------------+
| 2021-06-05 21:06:56 |
+---------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT HOUR(now());
+-------------+
| HOUR(now()) |
+-------------+
| 21 |
+-------------+
1 row in set (0.004 sec)
MariaDB [vlrinst]> SELECT LAST_DAY("2017-02-10 09:34:00");
+---------------------------------+
| LAST_DAY("2017-02-10 09:34:00") |
+---------------------------------+
| 2017-02-28 |
+---------------------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT LAST_DAY(now());
+-----------------+
| LAST_DAY(now()) |
+-----------------+
| 2021-06-30 |
+-----------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT LAST_DAY("2016-02-10 09:34:00");
+---------------------------------+
| LAST_DAY("2016-02-10 09:34:00") |
+---------------------------------+
| 2016-02-29 |
+---------------------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> select localtime();
+---------------------+
| localtime() |
+---------------------+
| 2021-06-05 21:16:35 |
+---------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> select now();
+---------------------+
| now() |
+---------------------+
| 2021-06-05 21:16:44 |
+---------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> select LOCALTIMESTAMP();
+---------------------+
| LOCALTIMESTAMP() |
+---------------------+
| 2021-06-05 21:17:18 |
+---------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT MAKEDATE(2021, 3);
+-------------------+
| MAKEDATE(2021, 3) |
+-------------------+
| 2021-01-03 |
+-------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT MAKEDATE(2021, 300);
+---------------------+
| MAKEDATE(2021, 300) |
+---------------------+
| 2021-10-27 |
+---------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT MAKEDATE(2000, 60);
+--------------------+
| MAKEDATE(2000, 60) |
+--------------------+
| 2000-02-29 |
+--------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT MAKEDATE(20001, 60);
+---------------------+
| MAKEDATE(20001, 60) |
+---------------------+
| NULL |
+---------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT MAKEDATE(2000, 60);
+--------------------+
| MAKEDATE(2000, 60) |
+--------------------+
| 2000-02-29 |
+--------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]>
Mounika@MOUNIKA-PC c:\xampp
# ram.cmd
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.4.18-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use vlrinst
Database changed
MariaDB [vlrinst]> SELECT MAKETIME(21, 59, 59);
+----------------------+
| MAKETIME(21, 59, 59) |
+----------------------+
| 21:59:59 |
+----------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT MAKETIME(25, 59, 59);
+----------------------+
| MAKETIME(25, 59, 59) |
+----------------------+
| 25:59:59 |
+----------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT MAKETIME(25, 59, 591);
+-----------------------+
| MAKETIME(25, 59, 591) |
+-----------------------+
| NULL |
+-----------------------+
1 row in set, 1 warning (0.000 sec)
MariaDB [vlrinst]> select MICROSECOND(now());
+--------------------+
| MICROSECOND(now()) |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT MICROSECOND("2017-06-20 09:34:00.000023");
+-------------------------------------------+
| MICROSECOND("2017-06-20 09:34:00.000023") |
+-------------------------------------------+
| 23 |
+-------------------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT minite("2017-06-20 09:34:00.000023");
ERROR 1305 (42000): FUNCTION vlrinst.minite does not exist
MariaDB [vlrinst]> SELECT minute("2017-06-20 09:34:00.000023");
+--------------------------------------+
| minute("2017-06-20 09:34:00.000023") |
+--------------------------------------+
| 34 |
+--------------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT minute(now());
+---------------+
| minute(now()) |
+---------------+
| 23 |
+---------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT month(now());
+--------------+
| month(now()) |
+--------------+
| 6 |
+--------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| June |
+------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> now()
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'now()' at line 1
MariaDB [vlrinst]> select now();
+---------------------+
| now() |
+---------------------+
| 2021-06-05 21:24:18 |
+---------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.