17:12

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Transact-SQL string and datetime functions

T-SQL Datetime functions

DATEADD function page 2

Taking the task 7.1.1 as example, let's consider addition of time interval to a date for some other DBMS

MySQL

MySQL has similar function with unsimilar parameters. Here is the syntax:

DATE_ADD(date, INTERVAL value addunit)

Where

date - the date which an interval is added to;

value - the interval value;

addunit - the interval type.

There are available the following interval types with selfdescribed names:


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

The solution of our task for MySQL takes the form:

SELECT DATE_ADD((SELECT MAX(date) FROM pass_in_trip), 
             interval 7 day) next_wd;

next_wd
2005-12-06 00:00:00

To add the interval consisting of few time components, you can use the substring of standard representation of date/time. So, let's add 1 day and 3 hours to '2018-01-27T13:00:00':

SELECT DATE_ADD('2018-01-27T13:00:00' , interval '1T3'  DAY_HOUR);


2018-01-28 16:00:00

Addition of 1 day and 15 seconds to the same date will be the following:

SELECT DATE_ADD('2018-01-27T13:00:00', interval '01T00:00:15'  DAY_SECOND);


2018-01-28 13:00:15

PostgreSQL and Oracle

These DBMS do not use any function for addition of intervals. In this case ordinary operator "+" is used:

SELECT MAX("date") + interval '7' day next_wd 
FROM pass_in_trip;

Take into account character data type for interval value.

Addition of 1 day and 3 hours

PostgreSQL

PostgreSQL has not composite intervals, so we should either express interval value in the terms of lesser interval

SELECT timestamp'2018-01-27T13:00:00' + interval '27'  hour;

or add two intervals

SELECT timestamp'2018-01-27T13:00:00' + interval '3' hour + interval '1' day;

In the same manner it could be done for addition of one day and 15 seconds, for example:

SELECT timestamp'2018-01-27T13:00:00' + interval '15' second + interval '1' day;

Oracle

Oracle uses composite intervals, for example (one day and 3 hours):

SELECT timestamp'2018-01-27 13:00:00' + interval '01 03'  DAY TO HOUR FROM dual;

or (1 day and 15 seconds):

SELECT timestamp'2018-01-27 13:00:00' + interval '01 00:00:15'  DAY TO SECOND FROM dual;

Naturally, we could add two simple intervals as in the case of PostgreSQL.

Pages

Oracle PostgreSQL MySQL DATEADD INTERVAL date_time functions DATE_ADD Composite intervals

Previous [T-SQL Datetime functions]

[DATEDIFF function] Next

Last added:

Tags

aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags

The book was updated
month ago

SQL Problems and solutions

S. I. Moiseenko

Transact-SQL string and datetime functions

T-SQL Datetime functions

DATEADD function page 2

MySQL

PostgreSQL and Oracle