SQL - Date functions. Transact-SQL functions Sql functions for working with dates

Getting the current date and time

To get the current date (without time) in YYYY-MM-DD format, you can use the functions:

• CURDATE()
• CURRENT_DATE()

To obtain the current time (without date) in the HH:MM:SS format, you can use the functions:

• CURTIME()
• CURRENT_TIME()

To obtain the current date and time in the YYYY-MM-DD HH:MM:SS format, you can use any of the following functions:

• NOW()
• SYSDATE()
• CURRENT_TIMESTAMP

Example 1

SELECT CURDATE(); or SELECT CURRENT_DATE();

Result: 2016-08-28

SELECT CURTIME(); or SELECT CURENT_TIME();

Result: 19:29:54

SELECT NOW(); or SELECT SYSDATE(); or SELECT CURRENT_TIMESTAMP;

Result: 2016-08-28 19:29:54

Adding and subtracting a time interval

With dates and times, you often have to perform arithmetic operations: add or subtract a certain time interval to a given date. Adding a time interval can be done in three ways:

• Function DATE_ADD(original_date, INTERVAL expression type);
• Function ADDDATE(original_date, INTERVAL expression type);
• Expression: source_date + INTERVAL expression type.

Subtracting a time interval can also be done in three ways:

• Function DATE_SUB(source_date, INTERVAL expression type);
• Function SUBDATE(source_date, INTERVAL expression type);
• Expression: source_date - INTERVAL expression type.

Source_date is a date to which a certain time interval is added or subtracted. Expression– this is the added or subtracted time interval itself, specified in text format. Type– an argument indicating the type of interval to be added. This option specifies how to correctly interpret the expression. For example, the expression '3:24' can be interpreted as either 3 hours 24 minutes or 3 minutes 24 seconds. If the type "MINUTE_SECOND" is specified, then the interpretation will be unambiguous. The relationship between the expression and type arguments is shown in the table:

Example 2

The next three commands do the same operation. They add a second to the given time.

SELECT "2016-09-10 23:59:59" + Interval 1 SECOND;

SELECT ADDDATE("2016-09-10 23:59:59", Interval 1 SECOND);

SELECT DATE_ADD("2016-09-10 23:59:59", Interval 1 SECOND);

The result of all three teams is the same: 2016-09-11 00:00:00.

Example 3

The following three commands calculate a date that is exactly one and a half years prior to a given one:

SELECT DATE_SUB("2016-09-10 23:59:59", Interval "1-6" YEAR_MONTH);

SELECT SUBDATE("2016-09-10 23:59:59", Interval "1-6" YEAR_MONTH);

SELECT "2016-09-10 23:59:59" - Interval "1-6" YEAR_MONTH;

The result of all three teams is the same: 2015-03-10 23:59:59.

Conversion functions to other units

Sometimes it is convenient to work with a date represented as the number of days that have passed since year 0. To convert a date to this format and back in MySQL, there are two functions:

• TO_DAYS(date) – returns the day number corresponding to the date argument.
• FROM_DAYS(day_number)– returns the date according to the day number.

From the author: Today we will talk about how date functions work in SQL. The following table provides a list of all the important date and time functions that are available. There are others supported by various DBMSs. This list represents the functions available in the MySQL DBMS.

ADDDATE(). Adds dates

ADDTIME(). Adds time

CONVERT_TZ(). Converts from one time zone to another

CURDATE(). Returns the current date

CURRENT_DATE(), CURRENT_DATE. Synonyms for CURDATE()

CURRENT_TIME(), CURRENT_TIME. Synonyms for CURTIME()

CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP. Synonyms for NOW()

CURTIME(). Returns the current time

DATE_ADD(). Adds two dates

DATE_FORMAT(). Sets the specified date format

DATE_SUB(). Subtracts one date from another

DATE(). Extracts the date part from an expression representing a date or time and date

DATEDIFF(). Subtracts one date from another

DAY(). Synonym for DAYOFMONTH()

DAYNAME(). Returns the day of the week

DAYOFMONT(). Returns the day of the month (1-31)

DAYOFWEEK(). Returns the index of the day of week of the argument

DAYOFYEAR(). Returns the number of the day in the year (1-366)

EXTRACT. Retrieves the date part

FROM_DAYS(). Converts a day number to a date

FROM_UNIXTIME(). Formats a date as a UNIX timestamp

HOUR(). Retrieves the hour

LAST_DAY. Returns the last day of the month for the argument

LOCALTIME(), LOCALTIME. Synonym for NOW()

LOCALTIMESTAMP, LOCALTIMESTAMP(). Synonym for NOW()

MAKEDATE(). Creates a date from the year and day of the year

MAKETIME. MAKETIME(). MICROSECOND(). Returns microseconds from argument

MINUTE(). Returns minutes from argument

MONTH(). Returning the month from a date

MONTHNAME(). Returns the name of the month

NOW(). Returns the current date and time

PERIOD_ADD(). Adds a period to the year-month

PERIOD_DIFF(). Returns the number of months between periods

QUARTER(). Returns the quarter from the argument

SEC_TO_TIME(). Converts seconds to "HH:MM:SS" format

SECOND(). Returns seconds (0-59)

STR_TO_DATE(). Converts a string to a date

SUBDATE(). When called with three arguments, the DATE_SUB() synonym

SUBTIME(). Subtracts time

SYSDATE(). Returns the execution time of a function

TIME_FORMAT(). Sets the time format

TIME_TO_SEC(). Returns the argument converted to seconds

TIME(). Extracts the time part of the passed expression

TimeDiff(). Subtracts time

TIMESTAMP(). With one argument, this function returns a date or datetime expression. With two arguments - adds these two arguments

TIMESTAMPADD(). Adds an interval to a date and time expression

TIMESTAMPDIFF(). Subtracts an interval from a datetime expression

TO_DAYS(). Returns the date argument converted to days

UNIX_TIMESTAMP(). Returns a UNIX timestamp

UTC_DATE(). Returns the current UTC date

UTC_TIME(). Returns the current UTC time

UTC_TIMESTAMP(). Returns the current UTC date and time

WEEK(). Returns the week number

WEEKDAY(). Returns the index of the day of the week

WEEKOFYEAR(). Returns the calendar week number (1-53)

YEAR(). Returns the year

YEARWEEK(). Returns the year and week

ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)

When called with a second argument specified as INTERVAL, ADDDATE() is a synonym for DATE_ADD(). The related function SUBDATE() is a synonym for DATE_SUB(). For information about the INTERVAL block argument, see DATE_ADD().

When called with the second argument given in days, MySQL treats this as an integer number of days to add to the expression.

ADDTIME(expr1,expr2)

ADDTIME() adds expr2 to expr1 and returns the result. Expr1 is a time or datetime expression, while expr2 is a time expression.

CONVERT_TZ(dt,from_tz,to_tz)

Converts the date and time value dt from the time zone specified in from_tz to the time zone specified in to_tz and returns the resulting value. This function returns NULL if the arguments are invalid.

CURDATE()

Returns the current date as a value in the format "YYYY-MM-DD" or YYYYMMDD, depending on whether this function is used in a string or numeric context.

CURRENT_DATE and CURRENT_DATE()

CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE()

CURTIME()

Returns the current time as a value in the format "HH:MM:SS" or HHMMSS, depending on whether the function is used in a string or numeric context. The value is expressed for the current time zone.

CURRENT_TIME and CURRENT_TIME()

CURRENT_TIME and CURRENT_TIME() are synonyms for CURTIME().

CURRENT_TIMESTAMP and CURRENT_TIMESTAMP()

CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are synonyms for NOW().

DATE(expr)

Retrieves the date part of a date or datetime expr expression.

DATEDIFF(expr1,expr2)

DATEDIFF() returns expr1.expr2 expressed as the number of days between two dates. Both expr1 and expr2 are date or datetime expressions. Only the date parts are used in the calculations.

DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)

These functions perform arithmetic operations on dates. date is represented as a DATETIME or DATE value indicating the start date. expr is an expression that specifies the interval value to add or subtract from the original date. expr is a string; it may start with "-" for negative intervals.

unit is a keyword that specifies the units of measure for the expression. The INTERVAL keyword and units notation are not case sensitive. The following table shows the expected form of the expr argument for each unit value.

The QUARTER and WEEK values ​​are available in MySQL since version 5.0.0.

DATE_FORMAT (date,format)

This command formats the date value according to the specified format string. The following pointers can be used in the format line. Format indicators must be preceded by a '%' character.

%a. Abbreviated name of the day of the week (Sun..Sat)

%b. Abbreviated month name (Jan..Dec)

%With. Numerical designation of the month (0…12)

%D. Day of the month with English suffix (0, 1, 2, 3,.)

%d. Numerical designation of the day of the month (00..31)

%e. Numerical designation of the day of the month (00..31)

%f. Microseconds (000000..999999)

%H. Hour (00..23)

%h. Hour (01..12)

%I. Hour (01..12)

%i. Numerical designation of minutes (00..59)

%J. Day of the year (001..366)

%k. Hour (0..23)

%l. Hour (1..12)

%M. Name of the month (January..December)

%m. Numerical designation of the month (00..12)

%R. AM or PM

%r. Time, 12-hour format (hh:mm:ss followed by AM or PM)

%S. Seconds (00..59)

%s. Seconds (00..59)

%T. Time, 24-hour format (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. Name of the day of the week (Sunday..Saturday)

%w. Day of the week (0=Sunday..6=Saturday)

%X. Year for a week where the first day of the week is Sunday, a four-digit number; used with %V

%x. Year for a week where the first day of the week is Monday, a four-digit number; used with %V

%Y. Year, date, four digits

%y. Numerical designation of the year (two digits)

%%. Literally the % symbol

%x. x, for all.x. not listed above

DATE_SUB(date,INTERVAL expr unit)

Similar to the DATE_ADD() function.

DAY(date)

DAY() is a synonym for the DAYOFMONTH() function.

DAYNAME(date)

Returns the day of the week for the specified date.

DAYOFMONTH(date)

Returns the day of the month for the specified date in the range 0 to 31.

DAYOFWEEK(date)

Returns the index of the day of the week (1 = Sunday, 2 = Monday, ., 7 = Saturday). These index values ​​follow the ODBC standard.

DAYOFYEAR(date)

Returns the day of the year for the specified date in the range 1 to 366.

EXTRACT(unit FROM date)

The EXTRACT() function uses the same types of unit indicators as DATE_ADD() or DATE_SUB(), but does not perform arithmetic operations on dates, but rather extracts the unit indicator part of the date.

FROM_DAYS(N)

The DATE value is returned taking into account the number of days N.

Note. Use FROM_DAYS() for old dates carefully. The function is not intended to work with date values ​​before the introduction of the Gregorian calendar (1582).

FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)

Returns a representation of the unix_timestamp argument as a value in the format "YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS" depending on whether this function is used in a string or numeric context. The value is expressed in the current time zone. The unix_timestamp parameter is an internal timestamp value that is generated by the UNIX_TIMESTAMP() function.

If format is specified, the result is formatted according to the format string, which is used in the same way as described in the DATE_FORMAT() section.

HOUR(time)

Returns the hours from the specified time. The range of the returned value is from 0 to 23. However, the range of TIME values ​​is actually much larger, so HOUR can return values ​​greater than 23.

LAST_DAY(date)

Takes a date or datetime value and returns the value corresponding to the last day of the month. Returns NULL if the argument is invalid.

LOCALTIME and LOCALTIME()

LOCALTIME and LOCALTIME() are synonyms for NOW().

LOCALTIMESTAMP and LOCALTIMESTAMP()

LOCALTIMESTAMP and LOCALTIMESTAMP() are synonyms for NOW().

MAKEDATE(year,dayofyear)

Returns the values ​​for the date, the specified year, and the day of the year. The value of dayofyear must be greater than 0 or the result will be NULL.

MAKETIME(hour,minute,second)

Returns the time value calculated from the arguments hour, minute and second.

MICROSECOND(expr)

Returns microseconds from a time expression or datetime(expr) expression as a number in the range 0 to 999999.

MINUTE(time)

Returns the minutes for the specified time, in the range 0 to 59.

MONTH(date)

Returns the month for the specified date in the range 0 to 12.

MONTHNAME(date)

Returns the full month name for the specified date.

NOW()

Returns the current date and time as a value in the format "YYYY-MM-DD HH:MM:SS" or YYYYMMDDHHMMSS, depending on whether the function is used in a string or numeric context. This value is expressed in the current time zone.

PERIOD_ADD(P,N)

Adds N months to period P (in YYMM or YYYYMM format). Returns a value in YYYYMM format. Note that the period argument P is not a date value.

PERIOD_DIFF(P1,P2)

Returns the number of months between periods P1 and P2. Periods P1 and P2 must be specified in the format YYMM or YYYYMM. Note that the period arguments P1 and P2 are not date values.

QUARTER(date)

Returns the quarter of the year for the specified date in the range 1 to 4.

SECOND(time)

Returns the seconds value for a time in the range 0 to 59.

SEC_TO_TIME(seconds)

Returns the seconds argument converted to hours, minutes, and seconds in the format "HH:MM:SS" or HHMMSS, depending on whether the function is used in a string or numeric context.

STR_TO_DATE(str,format)

This is the inverse of the DATE_FORMAT() function. It accepts the string str and the format string format. The STR_TO_DATE() function returns DATETIME if the format string contains both a date and time. Otherwise, it returns DATE or TIME if the string contains only a date or time.

SUBDATE(date,INTERVAL expr unit) and SUBDATE(expr,days)

If SUBDATE() is called with a second argument specified as INTERVAL, the function is a synonym for DATE_SUB(). For information about the INTERVAL argument, see DATE_ADD().

SUBTIME(expr1,expr2)

The SUBTIME() function returns expr1. expr2 is expressed as a value in the same format as expr1. The value of expr1 is a time or datetime expression, and the value of expr2 is a time expression.

SYSDATE()

Returns the current date and time as a value in the format "YYYY-MM-DD HH:MM:SS" or YYYYMMDDHHMMSS, depending on whether the function is used in a string or numeric context.

TIME(expr)

Extracts the time portion of expr and returns it as a string.

TIMEDIFF(expr1,expr2)

TIMEDIFF() returns expr1. expr2 is expressed as a time value. The values ​​expr1 and expr2 are either time or datetime expressions, but both must be of the same type.

TIMESTAMP (expr), TIMESTAMP (expr1, expr2)

With one argument specified, this function returns a date or datetime expression expr as a datetime value. With two arguments, it adds the time expression expr2 to the date or datetime expression expr1 and returns the result as a datetime value.

TIMESTAMPADD(unit,interval,datetime_expr)

This function adds an integer interval expression to a date or time expression datetime_expr. The units for the interval are specified by the unit argument, which can take one of the following values:

The unit value can be specified using one of the keywords as shown above, or with the SQL_TSI_ prefix. For example, DAY and SQL_TSI_DAY are valid values.

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Returns the integer difference between the date or datetime expressions datetime_expr1 and datetime_expr2. The units of measurement for the result are specified by the unit argument. The valid values ​​for the unit argument are the same as those listed in the TIMESTAMPADD() function.

TIME_FORMAT(time,format)

This function is used in the same way as the DATE_FORMAT() function, but the format string can only contain format specifiers for hours, minutes, and seconds.

If the time value contains an hour part that is greater than 23, the clock format indicators %H and %k give a value greater than the normal range of 0 to 23. Other clock format indicators give a value of modulo 12 hour.

TIME_TO_SEC(time)

Returns the time argument converted to seconds.

TO_DAYS(date)

Returns the day number (number of days since year 0) for the given date date.

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

If this function is called without an argument, it returns the Unix timestamp (seconds since "1970-01-01 00:00:00" UTC) as a positive integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument expressed in seconds since "1970-01-01 00:00:00" UTC. date can be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD.

UTC_DATE, UTC_DATE()

Returns the current UTC date as a value in the format "YYYY-MM-DD" or YYYYMMDD, depending on whether this function is used in a string or numeric context.

UTC_TIME, UTC_TIME()

Returns the current UTC time as a value in the format "HH:MM:SS" or HHMMSS, depending on whether the function is used in a string or numeric context.

UTC_TIMESTAMP, UTC_TIMESTAMP()

Returns the current UTC date and time as the value "YYYY-MM-DD HH:MM:SS" or in the format YYYYMMDDHHMMSS, depending on whether this function is used in a string or numeric context.

WEEK(date[,mode])

This function returns the week number for the given date date. The two-argument WEEK() form allows you to specify whether the week starts on Sunday or Monday, and whether the return value should be in the range 0 to 53 or 1 to 53. If the mode argument is omitted, the value of the default_week_format system variable is used

WEEKDAY(date)

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

WEEKOFYEAR(date)

Returns the calendar week for the given date date as a number in the range 1 to 53. WEEKOFYEAR() is a compatibility function equivalent to WEEK(date,3).

YEAR(date)

Returns the year for the given date, ranging from 1000 to 9999, or 0 for date.zero.

YEARWEEK(date), YEARWEEK(date,mode)

Returns the year and week for the given date date. The mode argument works exactly like the mode argument to the WEEK() function. The resulting year may be different from the year in the date argument for the first and last week of the year.

Note. The week number is different from what WEEK() will return (0) for the optional 0 or 1 arguments, because WEEK() will return the week in the context of a given year.

Last update: 07/29/2017

T-SQL provides a number of functions for working with dates and times:

GETDATE: Returns the current local date and time based on the system clock as a datetime object

SELECT GETDATE() -- 2017-07-28 21:34:55.830

GETUTCDATE : Returns the current local date and time in Greenwich Mean Time (UTC/GMT) as a datetime object

SELECT GETUTCDATE() -- 2017-07-28 18:34:55.830

SYSDATETIME: Returns the current local date and time based on the system clock, but differs from GETDATE in that the date and time is returned as a datetime2 object

SELECT SYSDATETIME() -- 2017-07-28 21:02:22.7446744

SYSUTCDATETIME : Returns the current local date and time in Greenwich Mean Time (UTC/GMT) as a datetime2 object

SELECT SYSUTCDATETIME() -- 2017-07-28 18:20:27.5202777

SYSDATETIMEOFFSET : returns a datetimeoffset(7) object that contains the date and time relative to GMT

SELECT SYSDATETIMEOFFSET() -- 2017-07-28 21:02:22.7446744 +03:00

DAY: returns the day of the date, which is passed as a parameter

SELECT DAY(GETDATE()) -- 28

MONTH : returns the month of a date

SELECT MONTH(GETDATE()) -- 7

YEAR : returns the year from a date

SELECT YEAR(GETDATE()) -- 2017

DATENAME: Returns the date portion as a string. The date part selection parameter is passed as the first parameter, and the date itself is passed as the second parameter:

SELECT DATENAME(month, GETDATE()) -- July

To determine part of a date, you can use the following parameters (their abbreviated versions are indicated in parentheses):

• year (yy, yyyy) : year

  quarter (qq, q) : quarter

  month (mm, m) : month

  dayofyear (dy, y) : day of the year

  day (dd, d) : day of the month

  week (wk, ww) : week

  weekday (dw) : day of the week

  hour (hh) : hour

  minute (mi, n) : minute

  second (ss, s) : second

  millisecond (ms) : millisecond

  microsecond (mcs) : microsecond

  nanosecond (ns) : nanosecond

  tzoffset (tz) : offset in minutes relative to GMT (for a datetimeoffset object)

DATEPART: Returns the date part as a number. The date part selection parameter is passed as the first parameter (the same parameters are used as for DATENAME), and the date itself is passed as the second parameter:

SELECT DATEPART(month, GETDATE()) -- 7

DATEADD: Returns a date that is the result of adding a number to a specific date component. The first parameter represents the date component described above for the DATENAME function. The second parameter is the amount to be added. The third parameter is the date itself, to which you need to add:

SELECT DATEADD(month, 2, "2017-7-28") -- 2017-09-28 00:00:00.000 SELECT DATEADD(day, 5, "2017-7-28") -- 2017-08-02 00 :00:00.000 SELECT DATEADD(day, -5, "2017-7-28") -- 2017-07-23 00:00:00.000

If the quantity added represents a negative number, then the date is actually decremented.

DATEDIFF: Returns the difference between two dates. The first parameter is the date component, which indicates in what units the difference should be measured. The second and third parameters are the dates being compared:

SELECT DATEDIFF(year, "2017-7-28", "2018-9-28") -- difference 1 year SELECT DATEDIFF(month, "2017-7-28", "2018-9-28") -- difference 14 months SELECT DATEDIFF(day, "2017-7-28", "2018-9-28") -- difference 427 days

TODATETIMEOFFSET : Returns a datetimeoffset value that is the result of adding the time offset to another datetimeoffset object

SELECT TODATETIMEOFFSET("2017-7-28 01:10:22", "+03:00")

SWITCHOFFSET: Returns a datetimeoffset value that is the result of adding the time offset to a datetime2 object

SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), "+02:30")

EOMONTH: Returns the date of the last day for the month that is used in the date passed as a parameter.

SELECT EOMONTH("2017-02-05") -- 2017-02-28 SELECT EOMONTH("2017-02-05", 3) -- 2017-05-31

As an optional second parameter, you can pass the number of months that need to be added to the date. Then the last day of the month will be calculated for the new date.

DATEFROMPARTS : by year, month and day creates a date

SELECT DATEFROMPARTS(2017, 7, 28) -- 2017-07-28

ISDATE: Tests whether an expression is a date. If it is, it returns 1, otherwise it returns 0.

SELECT ISDATE("2017-07-28") -- 1 SELECT ISDATE("2017-28-07") -- 0 SELECT ISDATE("07-28-2017") -- 0 SELECT ISDATE("SQL") - - 0

An example of using the functions is creating an order table that contains the order date:

CREATE TABLE Orders (Id INT IDENTITY PRIMARY KEY, ProductId INT NOT NULL, CustomerId INT NOT NULL, CreatedAt DATE NOT NULL DEFAULT GETDATE(), ProductCount INT DEFAULT 1, Price MONEY NOT NULL);

The DEFAULT GETDATE() expression specifies that if no date is supplied when adding data, it is automatically calculated using the GETDATE() function.

Another example - let's find orders that were made 16 days ago:

SELECT * FROM Orders WHERE DATEDIFF(day, CreatedAt, GETDATE()) = 16

Greetings, dear readers of the blog site. A database often needs to store various data related to date and time. This may be the date the information was added, the date of user registration, the time of last authorization and other data. IN SQL language There are many functions related to date and time, so today we’ll look at them.

All the functions discussed below work with calendar data types.

Getting the current date and time.

To obtain current date and time function is used NOW().

SELECT NOW()
Result: 2015-09-25 14:42:53

To receive only current date there is a function CURDATE().

SELECT CURDATE()
Result: 2015-09-25

And function CURTIME(), which returns only current time:

SELECT CURTIME()
Result: 14:42:53

The CURDATE() and NOW() functions are useful for adding records to a database for which you want to store the date they were added. For example, when adding an article to a website, it would be a good idea to store its publication date. Then the request to add an article to the database will be something like this:

INSERT INTO posts (id_post, text_post, date_publication) VALUES (1, "article text", NOW ());

Adding and subtracting dates and times

Function ADDDATE (date, INTERVAL value) adds to date date meaning value and returns the resulting value. The following values ​​can be used as value:

• SECOND - seconds
• MINUTE - minutes
• HOUR - hours
• DAY - days
• WEEK - weeks
• MONTH - months
• QUARTER - blocks
• YEAR - years

as well as their combinations:

• MINUTE_SECOND - minutes and seconds
• HOUR_SECOND hours - minutes and seconds
• HOUR_MINUTE - hours and minutes
• DAY_SECOND - days, hours, minutes and seconds
• DAY_MINUTE - days, hours and minutes
• DAY_HOUR - days and hours
• YEAR_MONTH - years and months.

SELECT ADDDATE ("2015-09-28 10:30:20", INTERVAL 1 DAY)
Result: 2015-09-29 10:30:20

SELECT ADDDATE ("2015-09-28 10:30:20", INTERVAL "3 1:20" DAY_MINUTE)
Result: 2015-10-01 11:50:20

Function SUBDATE (date, INTERVAL value) produces subtraction values from date date. Example:

SELECT SUBDATE ("2015-09-28 10:30:20", INTERVAL 20 HOUR)
Result: 2015-09-27 14:30:20

Function PERIOD_ADD(period, n) adds to the value period n months. The period value must be in YYYYMM format (for example, September 2015 would be 201509). Example:

SELECT PERIOD_ADD (201509, 4)
Result: 201601

Function TIMESTAMPADD(interval, n, date) adds to date date time interval n , whose values ​​are specified by the interval parameter. Possible values ​​for the interval parameter:

• FRAC_SECOND - microseconds
• SECOND - seconds
• MINUTE - minutes
• HOUR - hours
• DAY - days
• WEEK - weeks
• MONTH - months
• QUARTER - blocks
• YEAR - years

SELECT TIMESTAMPADD (QUARTER, 1, "2015-09-28")
Result: 2015-12-28

Function SUBTIME (date, time) subtracts from date date time time. Example:

SELECT SUBTIME("2015-09-28 10:30:20", "50:20:19")
Result: 2015-09-26 08:10:01

Calculating the interval between dates

Function TIMEDIFF(date1, date2) calculates the difference in hours, minutes and seconds between two dates date1 and date2 . Example:

SELECT TIMEDIFF("2015-09-28 10:30:20", "2015-09-29 10:30:20")
Result: -24:10:00

Function DATEDIFF(date1, date2) calculates difference in days between two dates, while hours, minutes and seconds are ignored when specifying dates. Example:

SELECT DATEDIFF("2015-09-28 00:00:20", "2015-09-27 23:40:20")
Result: 1

Using this function, it is easy to determine how many days have passed since the publication date of the article:

SELECT DATEDIFF (CURDATE(), date_publication) FROM posts WHERE id_post = 1

Function PERIOD_DIFF (period1, period2) calculates difference in months between two dates. Dates must be in YYYYMM format. For example, let’s find out how many months have passed from January 2015 to September 2015:

SELECT PERIOD_DIFF (201509, 201501)
Result: 9

Function TIMESTAMPDIFF(interval, date1, date2) calculates the difference between dates date2 and date1 in the units specified in the interval parameter. In this case, interval can take the following values:

• FRAC_SECOND - microseconds
• SECOND - seconds
• MINUTE - minutes
• HOUR - hours
• DAY - days
• WEEK - weeks
• MONTH - months
• QUARTER - blocks
• YEAR - years

SELECT TIMESTAMPDIFF (HOUR, "2015-09-28 10:30:20", "2015-09-28 19:50:20")
Result: 9

Retrieving various date and time formats and other information

Function DATE (datetime) returns date, cutting off time. Example:

SELECT DATE("2015-09-28 10:30:20")
Result: 2015-09-28

Function TIME (datetime) returns time, cutting off date. Example:

SELECT TIME ("2015-09-28 10:30:20")
Result: 10:30:20

Function TIMESTAMP (date) returns full format over time dates date . Example:

TIMESTAMP("2015-09-28")
Result: 2015-09-28 00:00:00

DAY (date) And DAYOFMONTH (date). Synonymous functions that return serial number of the day of the month. Example:

SELECT DAY("2015-09-28"), DAYOFMONTH("2015-09-28")
Result: 28 | 28

Functions DAYNAME (date),DAYOFWEEK (date) And WEEKDAY (date). The first function returns name of the day of the week, second - day of the week number(counting from 1 - Sunday to 7 - Saturday), the third is also the number of the day of the week, only another countdown (counting from 0 - Monday, to 6 - Sunday). Example:

SELECT DAYNAME("2015-09-28"), DAYOFWEEK("2015-09-28"), WEEKDAY("2015-09-28")
Result: Monday 2 | 0

Functions WEEK (date) And WEEKOFYEAR (datetime). Both functions return week number of the year, only the first week begins on Sunday, and the second on Monday. Example:

SELECT WEEK ("2015-09-28 10:30:20"), WEEKOFYEAR ("2015-09-28 10:30:20")
Result: 39 | 40

Function MONTH (date) returns numeric value of the month(from 1 to 12), and MONTHNAME (date) month name. Example:

SELECT MONTH("2015-09-28 10:30:20"), MONTHNAME("2015-09-28 10:30:20")
Result: 9 | September

Function QUARTER (date) returns block number years (from 1 to 4). Example:

SELECT QUARTER ("2015-09-28 10:30:20")
Result: 3

Function YEAR (date) returns year value(from 1000 to 9999). Example:

SELECT YEAR ("2015-09-28 10:30:20")
Result: 2015

Function DAYOFYEAR (date) returns serial number of the day per year (from 1 to 366). Primer:

SELECT DAYOFYEAR ("2015-09-28 10:30:20")
Result: 271

Function HOUR (datetime) returns hour value(from 0 to 23). Example:

SELECT HOUR ("2015-09-28 10:30:20")
Result: 10

Function MINUTE (datetime) returns minutes value(from 0 to 59). Example:

SELECT MINUTE ("2015-09-28 10:30:20")
Result: 30

Function SECOND (datetime) returns seconds value(from 0 to 59). Example:

SELECT SECOND ("2015-09-28 10:30:20")
Result: 20

Function EXTRACT (type FROM date) returns the date part specified by the type parameter. Example:

SELECT EXTRACT (YEAR FROM "2015-09-28 10:30:20"), EXTRACT (MONTH FROM "2015-09-28 10:30:20"), EXTRACT (DAY FROM "2015-09-28 10:30 :20"), EXTRACT (HOUR FROM "2015-09-28 10:30:20"), EXTRACT (MINUTE FROM "2015-09-28 10:30:20"), EXTRACT (SECOND FROM "2015-09- 28 10:30:20")
Result: 2015 | 9 | 28 | 10 | 30 | 20

Reciprocal functions TO_DAYS (date) And FROM_DAYS(n). First converts date to number of days, passed since the zero year. The second, on the contrary, accepts number of days, passed from year zero and converts them to date. Example:

SELECT TO_DAYS ("2015-09-28 10:30:20"), FROM_DAYS (736234)
Result: 736234 | 2015-09-28

Reciprocal functions UNIX_TIMESTAMP (date) And FROM_UNIXTIME(n). First converts date to number of seconds passed since January 1, 1970. The second, on the contrary, accepts number of seconds, from January 1, 1970 and converts them to date. Example:

SELECT UNIX_TIMESTAMP ("2015-09-28 10:30:20"), FROM_UNIXTIME (1443425420)
Result: 1443425420 | 2015-09-28 10:30:20

Reciprocal functions TIME_TO_SEC (time) And SEC_TO_TIME(n). First converts time to number of seconds, passed from the beginning of the day. The second, on the contrary, takes the number of seconds from the beginning of the day and converts them into time. Example:

SELECT TIME_TO_SEC ("10:30:20"), SEC_TO_TIME (37820)
Result: 37820 | 10:30:20

Function MAKEDATE (year, n) takes the year year and the number of the day in year n and converts them to a date. Example.

SQL working with dates– is so important that you cannot do without knowledge of basic sql operators in any worthwhile project. Whatever one may say, in all services there is a need to work with time. As a rule, this is the calculation of periods from one date to another, for example, displaying a list of registered users for a year, month, day, hour.

I want to give a number of solutions to common problems with working with dates in SQL, which I myself encountered every day, I hope this will be relevant and useful for you.

How to get current date in SQL
WHERE date = CURDATE()
or another option
WHERE date = STR_TO_DATE(now(), "%Y-%m-%d")

Add one hour to date in SQL
DATE_ADD("2013-03-30", INTERVAL 1 HOUR)

Add one day to a date in SQL
DATE_ADD("2013-03-30", INTERVAL 1 DAY)
Similarly, you can add any number of days to the current date.

Add one month to a date in SQL
DATE_ADD("2013-03-30", INTERVAL 1 MONTH)
Similarly, you can add any number of months to the current date.

Get yesterday's day in SQL
DATE_ADD(CURDATE(), INTERVAL -1 DAY)
or
DATE_SUB(CURDATE(), INTERVAL 1 DAY)

Get start date of current week in SQL
This is one of the most difficult tasks at first glance, but it can be solved very simply
CURDATE()-WEEKDAY(CURDATE());

Get a selection from this Monday to the current day of the week in SQL

Get a selection from the first day of the current month to the current day of the week in SQL
WHERE (date BETWEEN (CURDATE()-WEEKDAY(CURDATE())) AND CURDATE())

How to get user's date of birth in SQL
SELECT name, birth, CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(birth)) - (RIGHT(CURRENT_DATE,5)

Find all users whose birthday is next month in SQL
SELECT name, birth FROM user WHERE MONTH(birth) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));
or another option
SELECT name, birth FROM pet WHERE MONTH(birth) = MOD(MONTH(NOW()), 12) + 1;

In addition to the above cases on working with dates in SQL, I recommend that you read the documentation for the following operators:
NOW()– Returns the current date and time.
CURDATE()– Returns the current date.
CURTIME()– We return the current time.
DATE()– Consists of two parts date and time.
EXTRACT()– Returns a single date/time value.
DATE_ADD()– Adds the specified number of days/minutes/hours, etc. to the sample.
DATE_SUB()– Subtract the specified interval from the date.
DATEDIFF()– Returns the time value between two dates.
DATE_FORMAT()– Function for different output of time information.

Working with dates in SQl, as it turns out, is not so difficult, and now, instead of calculating periods using PHP, you can do this at the stage of executing the SQL query and get the necessary data selection.