MySQL Date Or Time Function Part-6

In MySQL Date Or Time Function Part-6 we will discuss about the PERIOD_ADD, PERIOD_DIFF, QUARTER, SECOND, SEC_TO_TIME, STR_TO_DATE, SUBDATE, SUBTIME

PERIOD_ADD Function:- PERIOD_ADD Function is used for adding a specific number of months to a period. Its return format will be YYYYMM

Syntax:- PERIOD_ADD(period, number)

Example:-

SELECT PERIOD_ADD(202203, 21);

Output:- 202312

PERIOD_DIFF Function:- PERIOD_DIFF Function is used for returning the difference between two periods. The output will be in a month.

Syntax:- PERIOD_DIFF(period1, period2)

Example:-

SELECT PERIOD_DIFF(2023, 1947);

Output:- -12

QUARTER Function:- QUARTER Function is used for returning the quarter of the year from a given date and time.

It returns 1 for January to March, 2 for April to June, 3 for July to September, and 4 for October to December.

Syntax:- QUARTER(datetime)

Example:-

SELECT QUARTER(“2023-02-14”);

Output:- 1

SEC_TO_TIME Function:- SEC_TO_TIME Function is used for returning time value from a given second value. The return value format will be HH:MM: SS

Syntax:- SEC_TO_TIME(seconds)

Example:-

SELECT SEC_TO_TIME(-3452);

Output:- -00:57:32

SELECT SEC_TO_TIME(98753);

Output:- 27:25:53

SECOND Function:- The SECOND Function is used for returning the second part from a given date and time.

Syntax:- SECOND(datetime)

Example:-

SELECT SECOND(“2023-02-14 03:14:40.000059”);

Output:- 40

STR_TO_DATE Function:- STR_TO_DATE Function is used for converting from date or DateTime string value to formatted date or DateTime value.

Syntax:- STR_TO_DATE(string, format)

Example:-

SELECT STR_TO_DATE(“FEB 13 2023”, “%M %d %Y”);

Output:- 2023-02-13

SUBDATE Function:- SUBDATE Function is used for subtracting time or date interval from a given date or time and then it returns subtracted date or time.

Syntax:- SUBDATE(date, INTERVAL date expression)

Required type of interval is any one of the following:-

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

Example:-

SELECT SUBDATE(“2023-02-14 03:37:47”, INTERVAL 5 HOUR);

Output:- 2023-02-13 22:37:47

SUBTIME Function:- SUBTIME Function is used for subtracting time from a given date or time and then it returns subtracted date or time.

Syntax:- SUBTIME(date/time, time interval)

Example:-

SELECT SUBTIME(“2023-02-14 3:42:11.000033”, “2:3:4.000006”);

Output:- 2023-02-14 01:39:07.000027

Read more detail about MySQL Date Or Time Function manual

Share with:

Leave a Reply