A tutorial on the use of time functions in MySQL

Unix_timestamp ()

Unix_timestamp (date)

Returns a UNIX timestamp (the number of seconds since ' 1970-01-01 00:00:00′gmt) If no parameter calls are invoked. If Unix_timestamp () with a

A date parameter is invoked, which returns the number of seconds from the ' 1970-01-01 00:00:00′gmt. Date can be a date string, a DateTime

A number that is a string, a timestamp, or a local time in YYMMDD or YYYYMMDD format.

Mysql> select Unix_timestamp ();

-> 882226357

Mysql> Select Unix_timestamp (' 1997-10-04 22:23:00′);

-> 875996580

When Unix_timestamp is used in a timestamp column, the function will accept the value directly, without the implied "String-to-unix-timestamp" transformation.

From_unixtime (Unix_timestamp)

Returns the value represented by the Unix_timestamp parameter in ' Yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS format, depending on whether the function is in a string

Or is used in a numeric context.

Mysql> Select From_unixtime (875996580);

-> ' 1997-10-04 22:23:00′

Mysql> Select From_unixtime (875996580) + 0;

-> 19971004222300

From_unixtime (Unix_timestamp,format)

Returns a String representing the Unix time token, formatted according to the format string. FORMAT can contain the bars listed with the Date_format () function

The same modifier for the same purpose.

Mysql> Select From_unixtime (Unix_timestamp (),

'%Y%d%m%h:%i:%s%x ');

-> ' 1997 23rd December 03:43:30 X '

MySQL DATEDIFF () function

Definitions and usage

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

Grammar

DATEDIFF (DATE1,DATE2)

The Date1 and Date2 parameters are valid date or date/time expressions.

Instance

Example 1

Use the following SELECT statement:

SELECT DATEDIFF (' 2008-12-30 ', ' 2008-12-29 ') as Diffdate

Results:

Diffdate

1

Example 2

Use the following SELECT statement:

SELECT DATEDIFF (' 2008-12-29 ', ' 2008-12-30 ') as Diffdate

Results:

Diffdate

-1

To_days:

To_days is the index of days in a year that returns date.

The following query selects all records and its date_col value is within the last 30 days:

Mysql> SELECT something from table

WHERE To_days (now ()) –to_days (date_col) <= 30;

DayOfWeek (date)

Returns the week index of date (1= Sunday, 2 = Monday, ...). 7= Saturday). These index values correspond to ODBC standards.

Mysql> Select DayOfWeek (' 1998-02-03′);

-> 3

Weekday (date)

Returns the week index of date (0= Monday, 1 = Tuesday, ...). 6= Sunday).

Mysql> Select Weekday (' 1997-10-04 22:23:00′);

-> 5

Mysql> Select Weekday (' 1997-11-05′);

-> 2

DayOfMonth (date)

Returns the date in the month of date, in the range 1 through 31.

Mysql> Select DayOfMonth (' 1998-02-03′);

-> 3

DayOfYear (date)

Returns the number of days in a year, in the range of 1 to 366.

Mysql> Select DayOfYear (' 1998-02-03′);

-> 34

MONTH (date)

Returns the month of date, ranging from 1 to 12.

Mysql> Select MONTH (' 1998-02-03′);

-> 2

Dayname (date)

Returns the name of the week of date.

Mysql> Select Dayname ("1998-02-05");

-> ' Thursday '

MonthName (date)

Returns the month name of the date.

Mysql> Select MonthName ("1998-02-05");

-> ' February '

Quarter (date)

Returns the quarter of date one year, ranging from 1 to 4.

Mysql> Select quarter (' 98-04-01′);

-> 2

WEEK (date)

WEEK (Date,first)

For Sunday is the first day of the week, there is a single parameter that returns the week number of date, ranging from 0 to 52. 2 Parameter Form week () allow

Do you specify whether the week starts in Sunday or Monday. If the second argument is 0, the week begins in Sunday, and if the second argument is 1,

Starting from Monday.

Mysql> Select WEEK (' 1998-02-20′);

-> 7

Mysql> Select WEEK (' 1998-02-20′,0);

-> 7

Mysql> Select WEEK (' 1998-02-20′,1);

-> 8

Year (date)

Returns the year of date, ranging from 1000 to 9999.

Mysql> Select year (' 98-02-03′);

-> 1998

HOUR (Time)

Returns the hour of time, ranging from 0 to 23.

Mysql> Select HOUR (' 10:05:03′);

-> 10

MINUTE (Time)

Returns the minutes of time, ranging from 0 to 59.

Mysql> Select MINUTE (' 98-02-03 10:05:03′);

-> 5

SECOND (Time)

The number of seconds to return time, ranging from 0 to 59.

Mysql> Select SECOND (' 10:05:03′);

-> 3

Period_add (P,n)

Increase n months to phase p (in format Yymm or yyyymm). Returns a value in YYYYMM format. Note that the phase parameter p is not a date value.

Mysql> Select Period_add (9801,2);

-> 199803

Period_diff (P1,P2)

Returns the number of months between periods P1 and P2, P1 and P2 should be in a format yymm or yyyymm. Note that the time parameter P1 and P2 are not date values.

Mysql> Select Period_diff (9802,199703);

-> 11

Date_add (Date,interval expr type)

Date_sub (Date,interval expr type)

Adddate (Date,interval expr type)

Subdate (Date,interval expr type)

These functions perform date operations. For MySQL 3.22, they are new. Adddate () and subdate () are synonyms for Date_add () and Date_sub ().