Unlock the Door to Dream Career

1. Generate datetime
2. Extraction
3. Format
4. Calculation

Generate datetime

NOW(): return current DateTime like YYYY-MM-DD HH:MI:SS

CURDATE(): return the current date like YYYY-MM-DD

CURTIME(): return time like HH:MI:SS

DATE_ADD(date, INTERVAL expr type): Adds a specified time interval to a date.

DATE_SUB(date, INTERVAL expr type): Subtracts a specified time interval from a date

SELECT Name, DATE_ADD(BirthTime, INTERVAL 1 YEAR) AS BirthTimeModified FROM Test;
SELECT Name, DATE_ADD(BirthTime, INTERVAL 30 DAY) AS BirthDayModified FROM Test;

DATEDIFF(date1, date2): Returns the number of days between two dates.

SELECT DATEDIFF('2017-01-13','2017-01-03') AS DateDiff; # 10

Extraction

DATE(date/DateTime): Extracts the date part of a date or date/time expression

• similar functions: YEAR(),YEARWEEK(), QUARTER(), MONTH(), MONTHNAME()[return English name], WEEK() WEEKOFYEAR(), WEEKDAY(), MINUTE(), SECOND()

SELECT Name, DATE(BirthTime) AS BirthDate
AS BirthDate FROM Test;
-- Output:
-- **Name   |** BirthDate
-- Pratik | 1996-09-26

SELECT 
    YEAR(created_at) AS order_year,
    MONTH(created_at) AS order_month,
    DAY(created_at) AS order_day,
    HOUR(created_at) AS order_hour,
    MINUTE(created_at) AS order_minute,
    SECOND(created_at) AS order_second
FROM orders;

EXTRACT(unit FROM date):

• unit can be MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR

SELECT EXTRACT(YEAR FROM created_at) AS order_year,
       EXTRACT(MONTH FROM created_at) AS order_month,
       EXTRACT(DAY FROM created_at) AS order_day
FROM orders;

Format

DATE_FORMAT(date, format): Displays date/time data in different formats.

• %a-Abbreviated weekday name (Sun-Sat)
• %b-Abbreviated month name (Jan-Dec)
• %c-Month, numeric (0-12)
• %D-Day of 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 the 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
• %u-Week (00-53) where Monday is the first day of the week
• %V-Week (01-53) where Sunday is the first day of the week, used with %X
• %v-Week (01-53) where Monday is the first day of the week, 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, four digits, used with %V
• %x-Year for the week where Monday is the first day of the week, four digits, used with %v
• %Y-Year, numeric, four digits
• %y-Year, numeric, two digits

DATE_FORMAT(created_at, '%Y-%m') = '2020-02'

STR_TO_DATE(): It takes a string and returns a date specified by a format mask.

SELECT STR_TO_DATE("JULY 18 2018", "%M %D %Y");

SEC_TO_TIME(): It converts numeric seconds into a time value.

SELECT SEC_TO_TIME(1); # 00:00:01

MAKEDATE(): It returns the date for a certain year and day-of-year value.

SELECT MAKEDATE(2009, 138); # 2009-05-18

MAKETIME(): It returns the time for a certain hour, minute, second combination.

SELECT MAKETIME(11, 35, 4); # 11:35:04

Calculation

TO_DAYS(): It converts a date into numeric days

SELECT TO_DAYS("2018-07-18"); # 737258

TIME_TO_SEC(): It converts a time value into numeric seconds

SELECT TIME_TO_SEC("09:16:10"); # 33370

DAYOFYEAR(): It returns the day of the year for a date value.

SELECT DAYOFYEAR("2018-07-16"); # 197