The PERIOD_ADD() function adds a specified number of months to a period.
The PERIOD_ADD() function will return the result formatted as YYYYMM.
SELECT PERIOD_ADD(202106, 15);
------------
The PERIOD_DIFF() function returns the difference between two periods. The result will be in months.
Note: period1 and period2 should be in the same format.
SELECT PERIOD_DIFF(202103, 202403);
------------
The QUARTER() function returns the quarter of the year for a given date value (a number from 1 to 4).
January-March returns 1
April-June returns 2
July-Sep returns 3
Oct-Dec returns 4
SELECT QUARTER(CURDATE());
---------
The SECOND() function returns the seconds part of a time/datetime (from 0 to 59).
----------
he SEC_TO_TIME() function returns a time value (in format HH:MM:SS) based on the specified seconds.
SELECT SEC_TO_TIME(200);
-------------
The STR_TO_DATE() function returns a date based on a string and a format.
STR_TO_DATE(string, format)
string Required. The string to be formatted to a date
format Required. The format to use. Can be one or a combination of the following values:
Format Description
%a Abbreviated weekday name (Sun to Sat)
%b Abbreviated month name (Jan to Dec)
%c Numeric month name (0 to 12)
%D Day of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, ...)
%d Day of the month as a numeric value (01 to 31)
%e Day of the month as a numeric value (0 to 31)
%f Microseconds (000000 to 999999)
%H Hour (00 to 23)
%h Hour (00 to 12)
%I Hour (00 to 12)
%i Minutes (00 to 59)
%j Day of the year (001 to 366)
%k Hour (0 to 23)
%l Hour (1 to 12)
%M Month name in full (January to December)
%m Month name as a numeric value (01 to 12)
%p AM or PM
%r Time in 12 hour AM or PM format (hh:mm:ss AM/PM)
%S Seconds (00 to 59)
%s Seconds (00 to 59)
%T Time in 24 hour format (hh:mm:ss)
%U Week where Sunday is the first day of the week (00 to 53)
%u Week where Monday is the first day of the week (00 to 53)
%V Week where Sunday is the first day of the week (01 to 53). Used with %X
%v Week where Monday is the first day of the week (01 to 53). Used with %X
%W Weekday name in full (Sunday to Saturday)
%w Day of the week where Sunday=0 and Saturday=6
%X Year for the week where Sunday is the first day of the week. Used with %V
%x Year for the week where Monday is the first day of the week. Used with %V
%Y Year as a numeric, 4-digit value
%y Year as a numeric, 2-digit value
SELECT STR_TO_DATE("August,6,2021", "%M %e %Y");
SELECT STR_TO_DATE("Monday, May 14, 2017", "%W %M %e %Y");
SELECT STR_TO_DATE("2017,8,14 10,40,10", "%Y,%m,%d %h,%i,%s");
------------
The SUBDATE() function subtracts a time/date interval from a date and then returns the date.
SUBDATE(date, INTERVAL value unit)
SELECT SUBDATE("2021-06-15 09:34:21", INTERVAL 15 MINUTE);
-------
The SUBTIME() function subtracts time from a time/datetime expression and then returns the new time/datetime.
SUBTIME(datetime, time_interval)
Subtract 5 days3 hours, 2 minutes, 5.000001 seconds and return the datetime:
SELECT SUBTIME("2021-06-05 10:24:21.000004", "5 3:2:5.000001");
------------------------
Mounika@MOUNIKA-PC c:\xampp
# ram.cmd
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
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]> PERIOD_ADD()SELECT PERIOD_ADD(202106, 15);
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 'PERIOD_ADD()SELECT PERIOD_ADD(202
106, 15)' at line 1
MariaDB [vlrinst]> SELECT PERIOD_ADD(202106, 15);
+------------------------+
| PERIOD_ADD(202106, 15) |
+------------------------+
| 202209 |
+------------------------+
1 row in set (0.041 sec)
MariaDB [vlrinst]> SELECT PERIOD_ADD(205006, 100);
+-------------------------+
| PERIOD_ADD(205006, 100) |
+-------------------------+
| 205810 |
+-------------------------+
1 row in set (0.007 sec)
MariaDB [vlrinst]> SELECT PERIOD_DIFF(202103, 202403);
+-----------------------------+
| PERIOD_DIFF(202103, 202403) |
+-----------------------------+
| -36 |
+-----------------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT PERIOD_DIFF(202103, 200003);
+-----------------------------+
| PERIOD_DIFF(202103, 200003) |
+-----------------------------+
| 252 |
+-----------------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT PERIOD_DIFF(2103, 0003);
+-------------------------+
| PERIOD_DIFF(2103, 0003) |
+-------------------------+
| 252 |
+-------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT QUARTER(CURDATE());
+--------------------+
| QUARTER(CURDATE()) |
+--------------------+
| 2 |
+--------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT second(CURDATE());
+-------------------+
| second(CURDATE()) |
+-------------------+
| 0 |
+-------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT second(CURDATE());
+-------------------+
| second(CURDATE()) |
+-------------------+
| 0 |
+-------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT second(now());
+---------------+
| second(now()) |
+---------------+
| 16 |
+---------------+
1 row in set (0.002 sec)
MariaDB [vlrinst]> select curdate();
+------------+
| curdate() |
+------------+
| 2021-06-05 |
+------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> select now();
+---------------------+
| now() |
+---------------------+
| 2021-06-05 23:23:46 |
+---------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT second(now()),now();
+---------------+---------------------+
| second(now()) | now() |
+---------------+---------------------+
| 0 | 2021-06-05 23:24:00 |
+---------------+---------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT second(now()),now();
+---------------+---------------------+
| second(now()) | now() |
+---------------+---------------------+
| 6 | 2021-06-05 23:24:06 |
+---------------+---------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT SEC_TO_TIME(200)
-> ;
+------------------+
| SEC_TO_TIME(200) |
+------------------+
| 00:03:20 |
+------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT SEC_TO_TIME(20000);
+--------------------+
| SEC_TO_TIME(20000) |
+--------------------+
| 05:33:20 |
+--------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT SEC_TO_TIME(200000);
+---------------------+
| SEC_TO_TIME(200000) |
+---------------------+
| 55:33:20 |
+---------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT STR_TO_DATE("August,6,2021", "%M %e %Y");
+------------------------------------------+
| STR_TO_DATE("August,6,2021", "%M %e %Y") |
+------------------------------------------+
| NULL |
+------------------------------------------+
1 row in set, 1 warning (0.040 sec)
MariaDB [vlrinst]> SELECT STR_TO_DATE("Monday, May 14, 2017", "%W %M %e %Y");
+----------------------------------------------------+
| STR_TO_DATE("Monday, May 14, 2017", "%W %M %e %Y") |
+----------------------------------------------------+
| NULL |
+----------------------------------------------------+
1 row in set, 1 warning (0.001 sec)
MariaDB [vlrinst]> SELECT STR_TO_DATE("2017,8,14 10,40,10", "%Y,%m,%d %h,%i,%s");
+--------------------------------------------------------+
| STR_TO_DATE("2017,8,14 10,40,10", "%Y,%m,%d %h,%i,%s") |
+--------------------------------------------------------+
| 2017-08-14 10:40:10 |
+--------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT STR_TO_DATE("August,6,2021", "%M, %e, %Y");
+--------------------------------------------+
| STR_TO_DATE("August,6,2021", "%M, %e, %Y") |
+--------------------------------------------+
| 2021-08-06 |
+--------------------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT STR_TO_DATE("Monday, May 14, 2017", "%W, %M, %e, %Y");
+-------------------------------------------------------+
| STR_TO_DATE("Monday, May 14, 2017", "%W, %M, %e, %Y") |
+-------------------------------------------------------+
| NULL |
+-------------------------------------------------------+
1 row in set, 1 warning (0.001 sec)
MariaDB [vlrinst]> SELECT STR_TO_DATE("Monday, May 14, 2017", "%W, %M %e, %Y");
+------------------------------------------------------+
| STR_TO_DATE("Monday, May 14, 2017", "%W, %M %e, %Y") |
+------------------------------------------------------+
| 2017-05-14 |
+------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT STR_TO_DATE("2017,8,14 10-4-10", "%Y,%m,%d %h,%i,%s");
+-------------------------------------------------------+
| STR_TO_DATE("2017,8,14 10-4-10", "%Y,%m,%d %h,%i,%s") |
+-------------------------------------------------------+
| NULL |
+-------------------------------------------------------+
1 row in set, 1 warning (0.001 sec)
MariaDB [vlrinst]> SELECT STR_TO_DATE("2017,8,14 10-4-10", "%Y,%m,%d %h-%i-%s");
+-------------------------------------------------------+
| STR_TO_DATE("2017,8,14 10-4-10", "%Y,%m,%d %h-%i-%s") |
+-------------------------------------------------------+
| 2017-08-14 10:04:10 |
+-------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> SELECT SUBTIME("2021-06-05 10:24:21.000004", "3:2:5.000001");
+-------------------------------------------------------+
| SUBTIME("2021-06-05 10:24:21.000004", "3:2:5.000001") |
+-------------------------------------------------------+
| 2021-06-05 07:22:16.000003 |
+-------------------------------------------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> SELECT SUBTIME("2021-06-05 10:24:21.000004", "5 3:2:5.000001");
+---------------------------------------------------------+
| SUBTIME("2021-06-05 10:24:21.000004", "5 3:2:5.000001") |
+---------------------------------------------------------+
| 2021-05-31 07:22:16.000003 |
+---------------------------------------------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.