-- Extract the microseconds from the date and time value given
SELECT MICROSECOND('12:00:00.123456') AS value1, 
    MICROSECOND('1997-12-31 23:59:59.000010') AS value2;

-- Get minute of the given time
SELECT MINUTE('98-02-03 10:05:03');

-- Get month for date
SELECT MONTH('1998-02-03'), MONTH('2008-00-00');

-- Get month name
SELECT MONTHNAME('1998-02-05');

/*
Return current time in 'YYYY-MM-DD HH:MM:SS' 
or YYYYMMDDHHMMSS.uuuuuu format
*/
SELECT NOW(), NOW() + 0;

/*
Two NOW() functions are used in the same statement.
They return the same value because NOW() returns the
time when the statement began to execute, rather than
the time when NOW() is called.
 
SLEEP is used to force the statement to wait for 2 seconds
before calling the second NOW() function.
 
The two NOW() functions returned the same value.
*/
SELECT NOW(), SLEEP(2), NOW();

/*
NOW() and SYSDATE() function are used in the same statement.
NOW() returns the time when the statement began to execute.
SYSDATE() returns the time when it is called.
 
SLEEP is used to force the statement to wait for 2 seconds
before calling SYSDATE() function.
 
NOW() and SYSDATE() returned different values.
*/
SELECT NOW(), SLEEP(2), SYSDATE();

/*
Add 2 months to a year-month in the format of
YYMM (9801) or YYYYMM (199801)
*/
SELECT PERIOD_ADD(9801,2), PERIOD_ADD(199801,2);

/*
Calculate the number of months between the first period
and the second period. Period is in the format of 
YYMM (9802) or YYYYMM (199703)
*/
SELECT PERIOD_DIFF(9802,199703);

-- Get the quarter for date 98-04-09.
SELECT QUARTER('98-04-09');

/*
If not used in arithmetic operation, SEC_TO_TIME returns 
a time in HH:MM:SS format by converting the number of 
seconds given to hour, minute, and second.
 
If used in arithmetic operation, SEC_TO_TIME returns 
a time in HHMMSS.000000 format by converting the number 
of seconds given to hour, minute, and second.
*/
SELECT SEC_TO_TIME(63239), SEC_TO_TIME(63239)+1;

-- Return second part for the given time
SELECT SECOND('10:05:03');

/*
Convert a date string from a format indicated by the 
given format to a DATETIME value. 
*/
SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');

/*
When used with three arguments, SUBDATE function
can be used to subtract any number of units - year,
month, day, hour, minute, second, or microsecond.
*/
SELECT SUBDATE('1998-01-02', INTERVAL 31 MONTH);

/*
When used only with two arguments, the second
argument is always number of days.
*/
SELECT SUBDATE('1998-01-02 12:00:00', 31);

/*
Subtract times and return a value in the same format as 
the first argument.
*/
SELECT SUBTIME('1997-12-31 23:59:59.999999','1 1:1:1.000002') AS time1,
    SUBTIME('01:00:00.999999', '02:00:00.999998') AS time2;

-- Format a time
SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');

-- Convert the time value in the argument 
-- to the number of seconds.
SELECT TIME_TO_SEC('22:23:00');

-- Extract time portion of the date or 
-- datetime argument passed in
SELECT TIME('2003-12-31 01:02:03') AS time1,
    TIME('2003-12-31 01:02:03.000123') AS time1;

-- Calculate time difference
SELECT TIMEDIFF('1997-12-31 23:59:59.000001',
    '1997-12-30 01:01:01.000002') AS Diff;

-- Returns a datetime value
SELECT TIMESTAMP('2003-12-31');

-- Adds a time value to a datetime value
SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');

-- Adds one week to datetime value
SELECT TIMESTAMPADD(WEEK,1,'2003-01-02 12:00:00');

/*
Subtract the month part in the second argument
from the month part in the first argument.
*/
SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');

/*
Returns a day number (the number of days since year 0). 
 
The argument is a date in either YYYY-MM-DD format,
YYYYMMDD format, or YYMMDD format. 
*/
SELECT TO_DAYS('1997-10-07'), TO_DAYS('19971007'), TO_DAYS('971007');

-- Get current Unix timestamp value
SELECT UNIX_TIMESTAMP();

/*
UNIX_TIMESTAMP can be called with an argument. The 
argument can be a DATE string, a DATETIME string, 
a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD.
*/
SELECT UNIX_TIMESTAMP('2007-11-30 10:30:19') AS datetime_format,
    UNIX_TIMESTAMP('2007-11-30') AS date_format,
    UNIX_TIMESTAMP('20071130') AS YYYYMMDD_format,
    UNIX_TIMESTAMP('071130') AS YYMMDD_format;

/*
Returns the current UTC date as a value in 'YYYY-MM-DD' format
if the function is used in a string context, or YYYYMMDD format
if used in numeric context such as arithmetic operation.
*/
SELECT UTC_DATE() AS "YYYY-MM-DD format", 
    UTC_DATE() + 0 AS "YYYYMMDD format";

/*
Returns the current UTC time as a value in 
'HH:MM:SS' format if the function is used 
in a string context, or HHMMSS.uuuuuu format
if used in numeric context such as arithmetic operation.
*/
SELECT UTC_TIME() AS "HH:MM:SS format", 
    UTC_TIME() + 0 AS "HHMMSS.uuuuuu format";

/*
Returns the current UTC date and time as a value in 
'YYYY-MM-DD HH:MM:SS' format if the function is used 
in a string context, or YYYYMMDDHHMMSS.uuuuuu format
if used in numeric context such as arithmetic operation.
*/
SELECT UTC_TIMESTAMP() AS "YYYY-MM-DD HH:MM:SS format", 
    UTC_TIMESTAMP() + 0 AS "YYYYMMDDHHMMSS.uuuuuu format";

-- Returns the week number.
SELECT WEEK('1998-02-20'), WEEK('1998-02-20',0), WEEK('1998-02-20',1);

/*
Returns weekday index. 0 = Monday, 1 = Tuesday, 2 = Wednesday, 
3 = Thursday, 4 = Friday, 5 = Saturday, 6 = Sunday
*/
SELECT WEEKDAY('1998-02-03 22:23:00'), WEEKDAY('1997-12-22');

/*
WEEKOFYEAR returns ISO calendar week number and is 
equivalent to WEEK function with a mode argument of 3.
*/
SELECT WEEKOFYEAR('2008-06-07'), WEEK('2008-06-07',3);

-- Returns year of a date in four different formats
SELECT YEAR('1998-02-03') AS "YYYY-MM-DD format", 
    YEAR('98-02-03') AS "YY-MM-DD format", 
    YEAR('19980203') AS "YYYYMMDD format", 
    YEAR('980203') AS "YYMMDD format";

-- Returns year and week for a date.
SELECT YEARWEEK('1987-01-01');