SQL Study Notes (5) --------- Time query statements and Study Notes

SQL Study Notes (5) --------- Time query statements and Study Notes
MySQL NOW () function definition and usage

The NOW () function returns the current date and time.

Example 1

The SELECT statement is as follows:

SELECT NOW(),CURDATE(),CURTIME()

The result is similar:

NOW ()	CURDATE ()	CURTIME ()
16:25:46	2008-12-29	16:25:46

Example 2

The following SQL statement creates an "Orders" table with the date and time column (OrderDate:

CREATE TABLE Orders (OrderId int NOT NULL,ProductName varchar(50) NOT NULL,OrderDate datetime NOT NULL DEFAULT NOW(),PRIMARY KEY (OrderId))

Note that the OrderDate column specifies NOW () as the default value. As a result, when you insert rows to the table, the current date and time are automatically inserted into the column.

Now, we want to insert a new record in the "Orders" table:

INSERT INTO Orders (ProductName) VALUES ('Computer')

The "Orders" table will look like this:

 

OrderId	ProductName	OrderDate
1	'Computer'	16:25:46. 635

 

MySQL EXTRACT () function

 

Definition and usage

The EXTRACT () function is used to return a separate part of a date or time, such as year, month, day, hour, or minute.

Syntax

EXTRACT(unit FROM date)

DateA parameter is a valid date expression.UnitParameters can be the following values:

Unit Value
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

Instance

Suppose we have the following table:

OrderId	ProductName	OrderDate
1	'Computer'	16:25:46. 635

We use the following SELECT statement:

SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear,EXTRACT(MONTH FROM OrderDate) AS OrderMonth,EXTRACT(DAY FROM OrderDate) AS OrderDayFROM OrdersWHERE OrderId=1

Result:

OrderYear	OrderMonth	OrderDay
2008	12	29

MySQL CURDATE () function definition and usage

The CURDATE () function returns the current date.

Example 1

The SELECT statement is as follows:

SELECT NOW(),CURDATE(),CURTIME()

The result is similar:

NOW ()	CURDATE ()	CURTIME ()
16:25:46	2008-12-29	16:25:46

Example 2

The following SQL statement creates an "Orders" table with the date and time column (OrderDate:

CREATE TABLE Orders (OrderId int NOT NULL,ProductName varchar(50) NOT NULL,OrderDate datetime NOT NULL DEFAULT CURDATE(),PRIMARY KEY (OrderId))

Note that the OrderDate column specifies CURDATE () as the default value. As a result, when you insert rows to the table, the current date and time are automatically inserted into the column.

Now, we want to insert a new record in the "Orders" table:

INSERT INTO Orders (ProductName) VALUES ('Computer')

The "Orders" table will look like this:

OrderId	ProductName	OrderDate
1	'Computer'	2008-12-29

 

MySQL CURTIME () function

 

Definition and usage

The CURTIME () function returns the current time.

Instance

The SELECT statement is as follows:

SELECT NOW(),CURDATE(),CURTIME()

The result is similar:

NOW ()	CURDATE ()	CURTIME ()
16:25:46	2008-12-29	16:25:46

 

 

 

MySQL DATE_ADD () function

Definition and usage

The DATE_ADD () function adds a specified interval to a date.

Syntax

DATE_ADD(date,INTERVAL expr type)

DateA parameter is a valid date expression.ExprThe parameter is the time interval you want to add.

The type parameter can be of the following values:

Type Value
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

Instance

Suppose we have the following table:

OrderId	ProductName	OrderDate
1	'Computer'	16:25:46. 635

Now, we want to add 2 days to "OrderDate" so that we can find the payment date.

We use the following SELECT statement:

SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 2 DAY) AS OrderPayDateFROM Orders

Result:

OrderId	OrderPayDate
1	16:25:46. 635

 

 

 

MySQL DATE_FORMAT () function

 

Definition and usage

The DATE_FORMAT () function is used to display date/time data in different formats.

Syntax

DATE_FORMAT(date,format)

DateThe parameter is a valid date.FormatSpecifies the output format of the date/time.

The following formats are available:

Format	Description
%	Abbreviated name of a week
% B	Abbreviated month name
% C	Month, Value
% D	Day of the month with an English prefix
% D	Day of the month, numeric value (00-31)
% E	Day of the month, value (0-31)
% F	Microseconds
% H	Hour (00-23)
% H	Hour (01-12)
% I	Hour (01-12)
% I	Minute, value (00-59)
% J	Day of the year (001-366)
% K	Hour (0-23)
% L	Hour (1-12)
% M	Month name
% M	Month, value (00-12)
% P	AM or PM
% R	Time, 12-hour (hh: mm: ss AM or PM)
% S	Seconds (00-59)
% S	Seconds (00-59)
% T	Time, 24-hour (hh: mm: ss)
% U	Week (00-53) Sunday is the first day of the week
% U	Week (00-53) Monday is the first day of the week
% V	Week (01-53) Sunday is the first day of the week, used with % X
% V	Week (01-53) Monday is the first day of the week, used with % x
% W	Week name
% W	Day of the week (0 = Sunday, 6 = Saturday)
% X	Year, where Sunday is the first day of the week, four digits, used with % V
% X	Year, where Monday is the first day of the week, 4 digits, used with % v
% Y	Year, 4 digits
% Y	Year, two places

Instance

The following script uses the DATE_FORMAT () function to display different formats. NOW () is used to obtain the current date/time:

DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')DATE_FORMAT(NOW(),'%m-%d-%Y')DATE_FORMAT(NOW(),'%d %b %y')DATE_FORMAT(NOW(),'%d %b %Y %T:%f')

The result is similar:

Dec 29 2008 11:45 PM12-29-200829 Dec 0829 Dec 2008 16:25:46.635

 

 

MySQL DATE_SUB () function

 

Definition and usage

The DATE_SUB () function minus the specified time interval from date.

Syntax

DATE_SUB(date,INTERVAL expr type)

DateA parameter is a valid date expression.ExprThe parameter is the time interval you want to add.

The type parameter can be of the following values:

Type Value
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

Instance

Suppose we have the following table:

OrderId	ProductName	OrderDate
1	'Computer'	16:25:46. 635

Now, we want to subtract 2 days from "OrderDate.

We use the following SELECT statement:

SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDateFROM Orders

Result:

OrderId	OrderPayDate
1	2008-12-27 16:25:46. 635

 

 

 

MySQL DATEDIFF () function

 

Definition and usage

The DATEDIFF () function returns the number of days between two dates.

Syntax

DATEDIFF(date1,date2)

Date1AndDate2A parameter is a valid date or date/time expression.

Note: Only the date part of the value is involved in calculation.

Example 1

Use the following SELECT statement:

SELECT DATEDIFF('2008-12-30','2008-12-29') AS DiffDate

Result:

DiffDate
1

Example 2

Use the following SELECT statement:

SELECT DATEDIFF('2008-12-29','2008-12-30') AS DiffDate

Result:

DiffDate
-1

 

 

MySQL DATE () function

 

Definition and usage

The DATE () function returns the DATE part of the DATE or DATE/time expression.

Syntax

DATE(date)

DateA parameter is a valid date expression.

Instance

Suppose we have the following table:

 

OrderId	ProductName	OrderDate
1	'Computer'	16:25:46. 635

We use the following SELECT statement:

 

SELECT ProductName, DATE(OrderDate) AS OrderDateFROM OrdersWHERE OrderId=1

Result:

 

ProductName	OrderDate
'Computer'	2008-12-29

This document draws on w3cschool