loading..
Русский    English
02:02

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:

  1. 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:

  1. SELECT DATE_ADD((SELECT MAX(date) FROM pass_in_trip),
  2.              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':

  1. 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:

  1. 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:

  1. SELECT MAX("date") + interval '7' day next_wd
  2. 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

  1. SELECT timestamp'2018-01-27T13:00:00' + interval '27'  hour;
or add two intervals

  1. 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:

  1. 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):

  1. SELECT timestamp'2018-01-27 13:00:00' + interval '01 03'  DAY TO HOUR FROM dual;
or (1 day and 15 seconds):

  1. 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.

Bookmark and Share
Pages 1 2
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 date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100