Date and Time Operators

When you use date and time operators, add prefixes of explicit types to the operands to ensure that the operands can be properly parsed by the database.
For example, the operand in the following example does not have an explicit data type, and an error will occur.
1
|
SELECT date '2001-10-01' - '7' AS RESULT; |
Operator |
Example |
Result: |
||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
+ |
date + integer → timestamp Add days to a date.
|
Add a date with an integer to obtain the time after 7 days.
|
||||||||||||
+ |
date + interval → timestamp Add an interval to a date.
|
Sum of the date parameter and the interval parameter Obtain the time after one hour.
Obtain the time after one month.
If the sum or subtraction results fall beyond the date range of a month, the result will be rounded to the last day of the month.
|
||||||||||||
+ |
date + time → timestamp Add the time of a day to a date.
|
Add a date and a time to obtain a specific time.
|
||||||||||||
+ |
interval + interval → interval Add an interval.
|
Add two intervals to obtain the sum.
|
||||||||||||
+ |
timestamp + interval → timestamp Add an interval to a timestamp.
|
Add a timestamp and an interval to obtain the time after 23 hours.
|
||||||||||||
+ |
time + interval → time Add an interval to a time.
|
Add a time and an interval to obtain the time after three hours.
|
||||||||||||
- |
date - date → integer Subtract a date from another to obtain the time difference.
|
Subtract a date from another to obtain the time difference.
|
||||||||||||
- |
date - integer → timestamp Subtract days from a date.
|
Subtract an integer from a date to obtain the time difference between them.
|
||||||||||||
- |
date - interval → timestamp Subtract an interval from a date.
|
Subtract an interval from a date to obtain the date and time difference.
|
||||||||||||
- |
time - time → interval Subtract a time.
|
Subtract a time from another time to obtain the time difference.
|
||||||||||||
- |
time - interval → time Subtract an interval from a time.
|
Subtract an interval from a time to obtain the time difference.
|
||||||||||||
- |
timestamp - interval → timestamp Subtract an interval from a timestamp.
|
Subtract an interval from a timestamp to obtain the date and time difference.
|
||||||||||||
- |
interval - interval → interval Subtract an interval.
|
Subtract an interval from another interval to obtain the time difference.
|
||||||||||||
- |
timestamp - timestamp → interval Subtract a timestamp.
|
Subtract a timestamp from another timestamp to obtain the time difference.
|
||||||||||||
- |
select now() - interval → timestamp with time zone Obtain the time on the previous day.
|
Obtain the time on the previous day.
|
||||||||||||
* |
interval * double precision → interval Multiply an interval by a quantity.
|
Multiply the interval of 1 second by 900.
Multiply the interval of one day by 21.
Multiply the interval of one hour by 3.5.
|
||||||||||||
/ |
interval / double precision → interval
|
Divide an interval by a quantity to obtain a time segment.
|
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.