Commonly used date calculation statements in SQL

In mysql, there are a large number of functions for date calculation, such as DATE_FORMAT, UNIX_TIMESTAMP, date, and other related date functions. Below I will summarize several commonly used mysql date operation functions, thank you for your reference.

1. Calculate the age

If you have a person's birthday and need to calculate the person's age, replace @ dateofbirth with the person's birthday in the following statement.

The Code is as follows:	Copy code
SELECT DATE_FORMAT (FROM_DAYS (TO_DAYS (now ()-TO_DAYS (@ dateofbirth), '% y') + 0;

2. Calculate the difference between the two dates

Calculate the difference between the minute, second, hour, and day of the two dates. If the format of dt1 and dt2 is 'yyyy-mm-dd hh: mm: ss ', the second difference between two dates is

The Code is as follows:	Copy code
UNIX_TIMESTAMP (dt2)-UNIX_TIMESTAMP (dt1)

3. Show the column values that appear N times

The Code is as follows:	Copy code
SELECT id FROM tbl Group by id Having count (*) = N;

4. Calculate the workday between two dates

The simplest way to calculate the workday between two dates is a calendar table containing the dday section and the other that marks whether all the dates in a known year are off days, then the following query finds all workdays between Start and Stop.

The Code is as follows:	Copy code
Select count (*) FROM calendar WHERE d BETWEEN Start AND Stop And dayofweek (d) not in (1, 7) AND holiday = 0;

5. unix timestamp comparison

Mysql built-in function: UNIX_TIMESTAMP ()

If no parameter is called, a number of seconds (unsigned integer) from '2017-01-01 00:00:00 'to the current time is returned by default ). If you use date to call unix_timestamp (), it will return the number of seconds from '2017-01-01 00:00:00 'to the time point represented by date; date can be a date string, A datetime string, a timestamp, or a number in the yymmdd or yyymmdd format of the local time.

The SQL statement can be written as follows:

The Code is as follows:	Copy code
Select id, title, content, addtime from tablename_tbl where UNIX_TIMESTAMP (addtime)> = UNIX_TIMESTAMP ()-10800 and UNIX_TIMESTAMP (addtime) <= UNIX_TIMESTAMP () // 10800 is the number of seconds in 3 hours.

You can also write it like this.

The Code is as follows:	Copy code
Select * from tb where c> date_format ('2017-07-06 ',' % Y % m % D') and c <= date_format ('2017-07-09 ', '% Y % m % D '); Select * from tb where c> date ('2017-07-07 ') and c <date ('2017-07-09 ')

For mysql date comparison, refer to http://www.bKjia. c0m/database/mysql/40632.htm