InterSystems IRIS Data Platform 2019.2  /  InterSystems SQL Reference  /  SQL Functions

InterSystems SQL Reference
Previous section           Next section
InterSystems: The power behind what matters   

A scalar date/time function that returns a new timestamp calculated by adding a number of intervals of a specified date part to a timestamp.
{fn TIMESTAMPADD(interval-type,integer-exp,timestamp-exp)}
interval-type The type of time/date interval that integer-exp represents, specified as a keyword.
integer-exp An integer value expression that is to be added to timestamp-exp.
timestamp-exp A timestamp value expression, which will be increased by the value of integer-exp.
The TIMESTAMPADD function modifies a date/time expression by incrementing the specified date part by the specified number of units. For example, if interval-type is SQL_TSI_MONTH and integer-exp is 5, TIMESTAMPADD increments timestamp-exp by five months. You can also decrement a date part by specifying a negative integer for integer-exp.
TIMESTAMPADD returns a timestamp of the same data type as the input timestamp-exp. This timestamp can be in either %Library.TimeStamp data type format (yyyy-mm-dd hh:mm:ss.ffff) or %Library.PosixTime data type format (an encoded 64-bit signed integer).
Note that TIMESTAMPADD can only be used as an ODBC scalar function (with the curly brace syntax).
Similar time/date modification operations can be performed on a timestamp using the DATEADD general function.
Interval Types
The interval-type argument can be one of the following timestamp intervals:
These timestamp intervals may be specified with or without enclosing quotation marks, using single quotes or double quotes. They are not case-sensitive.
Incrementing or decrementing a timestamp interval causes other intervals to be modified appropriately. For example, incrementing the hour past midnight automatically increments the day, which may in turn increment the month, and so forth. TIMESTAMPADD always returns a valid date, taking into account the number of days in a month, and calculating for leap year. For example, incrementing January 31 by one month returns February 28 (the highest valid date in the month), unless the specified year is a leap year, in which case it returns February 29.
You can increment or decrement by fractional seconds of three digits of precision. Specify fractional seconds as an integer count of thousandths of a second (001 through 999).
DATEADD and TIMESTAMPADD handle quarters (3-month intervals); DATEDIFF and TIMESTAMPDIFF do not handle quarters.
%TimeStamp Format
If the timestamp-exp argument is in %Library.TimeStamp data type format (yyyy-mm-dd hh:mm:ss.ffff) the following rules apply:
Range and Value Checking
TIMESTAMPADD performs the following checks on %Library.TimeStamp input values:
The following example adds 1 week to the original timestamp:
SELECT {fn TIMESTAMPADD(SQL_TSI_WEEK,1,'2017-12-20 12:00:00')}
it returns 2017-12-27 12:00:00, because adding 1 week adds 7 days.
The following example adds 5 months to the original timestamp:
SELECT {fn TIMESTAMPADD(SQL_TSI_MONTH,5,'2017-12-20 12:00:00')}
returns 2018-05-20 12:00:00 because in this case adding 5 months also increments the year.
The following example also adds 5 months to the original timestamp:
SELECT {fn TIMESTAMPADD(SQL_TSI_MONTH,5,'2018-01-31 12:00:00')}
it returns 2018-06-30 12:00:00. Here TIMESTAMPADD modified the day value as well as the month, because simply incrementing the month would result in June 31, which is an invalid date.
The following example increments the original timestamp by 45 minutes:
SELECT {fn TIMESTAMPADD(SQL_TSI_MINUTE,45,'2017-12-20 00:00:00')}
returns 2017-12-20 00:45:00.
The following example decrements the original timestamp by 45 minutes:
SELECT {fn TIMESTAMPADD(SQL_TSI_MINUTE,-45,'2017-12-20 00:00:00')}
it returns 2017-12-19 23:15:00. Note that in this case decrementing the time also decremented the day.
See Also

Previous section           Next section
Send us comments on this page
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-09-19 06:56:35