Intervals
Intervals - Introduction and Use Cases
For a simple tutorial on the Postgresql interval data type, there are
several online articles, see, e.g. the official documentation at
https://www.postgresql.org/docs/current/static/datatype-datetime.html,
https://www.postgresql.org/docs/current/static/functions-datetime.html
http://www.postgresqltutorial.com/postgresql-interval/
Intervals are a way of adding, subtracting, etc a time period with a date.
Sample Interval
Just to see what an interval looks like returned from a query using s-sql. Note that the interval parameter is a string within a form provided to the :interval sql-op.
(query (:select (:interval "6 years 5 months 4 days 3 hours 2 minutes 1 second"))
:single)
((:MONTHS 77) (:DAYS 4) (:SECONDS 10921) (:USECONDS 0))
Allowable Formats of the parameter to Interval
There are four different formats available for providing the interval parameter.
Choose whichever one you like:
SQL Standard Format
(query (:select (:interval "1-2"))
:single)
((:MONTHS 14) (:DAYS 0) (:SECONDS 0) (:USECONDS 0))
Traditional Postgresql Format
(query (:select (:interval "3 4:05:06"))
:single)
((:MONTHS 0) (:DAYS 3) (:SECONDS 14706) (:USECONDS 0))
ISO 8601 Format with Designators
(query (:select (:interval "1 year 2 months 3 days 4 hours 5 minutes 6 seconds"))
:single)
((:MONTHS 14) (:DAYS 3) (:SECONDS 14706) (:USECONDS 0))
ISO 8601 Alternative Format
(query (:select (:interval "P0001-02-03T04:05:06"))
:single)
((:MONTHS 14) (:DAYS 3) (:SECONDS 14706) (:USECONDS 0))
Formatting Intervals to String
You can use the :to-char sql-op with a format string to provide a single
string version of the date/time. The ISO 8601 interval format allows
| Abbreviation | Description |
| Y | Year |
| M | Months |
| W | Weeks |
| D | Days |
| H | Hours |
| M | Minutes |
| S | Seconds |
where hours can be specified to 12 or 24.
(query
(:select
(:to-char
(:interval "6 years 5 months 4 days 3 hours 2 minutes 1 second") "YYYY:MM:DD:HH24:MI:SS"))
:single)
"0006:05:04:03:02:01"
(query
(:select
(:to-char
(:interval "6 years 5 months 4 days 3 hours 2 minutes 1 second") "HH24:MI:SS"))
:single)
"03:02:01"
Just to be different, we can add weeks and days instead of years and months. E.g.
(query
(:select
(:to-char
(:interval "6 years 5 months 4 days 3 hours 2 minutes 1 second") "WW:DD:HH24:MI:SS"))
:single)
"331:04:03:02:01"
Math with Intervals
Adding or subtracting intervals from each other will result in an interval.
(query (:select (:+ (:interval "2h 50min") (:interval "10min")))
:single)
((:MONTHS 0) (:DAYS 0) (:SECONDS 10800) (:USECONDS 0))
The difference between two timestamps is always an interval. Note: you
cannot add, multiple or divide two timestamps.
(query
(:select (:- (:timestamp "1999-12-30") (:timestamp "1999-12-11")))
:single)
((:MONTHS 0) (:DAYS 19) (:SECONDS 0) (:USECONDS 0))
The difference between two dates is an integer number of days, not an
interval.
Adding or subtracting an interval from another date will provide a universal time
which you can convert into a string using to-char E.g
(
query
(:select (:- (:now) (:interval ("6 years 5 months 4 days 3 hours 2 minutes 1 second"))))
:single)
3540933266
(query
(:select
(:to-char
(:+ (:date "2016-12-31") (:interval "25 hours"))
"YYYY-MM-DD"))
:single)
"2017-01-01"
(query
(:select
(:to-char
(:- (:now) (:interval "6 years 5 months 4 days 3 hours 2 minutes 1 second"))
"YYYY-MM-DD hh24:mm:ss"))
:single)
"2012-03-19 10:03:53"
They can be cast back to the alist interval style:
(query
(:select
(:type (:- (:timestamp "2016-12-31 03:00") (:timestamp "2016-12-29 13:00"))
interval))
:single)
((:MONTHS 0) (:DAYS 1) (:SECONDS 50400) (:USECONDS
Or you can use local-time or simple-date to convert the result into some
type of timestamp.
(local-time:universal-to-timestamp
(query (:select (:- (:now) (:interval "6 years 5 months 4 days 3 hours 2 minutes 1 second")))
:single))
@2012-03-16T17:35:32.000000-07:00
(simple-date:universal-time-to-timestamp
(query (:select (:- (:now) (:interval "6 years 5 months 4 days 3 hours 2 minutes 1 second")))
:single))
#<SIMPLE-DATE:TIMESTAMP 17-03-2012T00:36:14>
You can also do the more expected sums and groupings using intervals. In
the following example, we sum the total employment days by city of the
employees with more than 1 year of service.
(query
(:select 'city (:as (:sum (:- (:timestamp "2018-04-10")
'start-date))
'total-days)
:from 'employee
:group-by 'city
:having (:> (:sum (:- (:timestamp "2018-04-10") 'start-date))
(:interval "1 year"))))
(("Vancouver" ((:MONTHS 0) (:DAYS 21746) (:SECONDS 0) (:USECONDS 0)))
("New York" ((:MONTHS 0) (:DAYS 22751) (:SECONDS 0) (:USECONDS 0)))
("Toronto" ((:MONTHS 0) (:DAYS 20374) (:SECONDS 0) (:USECONDS 0))))
Extracting Subparts
You can extract a part of an interval using :extract.
(query (:select (:extract "minute" (:interval "5 hours 21 minutes")))
:single)
21.0d0
(query (:select (:extract "hour" (:interval "35 hours 21 minutes")))
:single)
35.0d0
(query (:select (:extract "day" (:interval "6 years 5 months 4 days 3 hours 2 minutes 1 second")))
:single)
4.0d0
(query (:select (:extract "year" (:interval "6 years 5 months 4 days 3 hours 2 minutes 1 second")))
:single)
6.0d0
(query (:select (:extract "year" (:interval "6 years 15 months 4 days 3 hours 2 minutes 1 second")))
:single)
7.0d0
(query (:select (:extract "month" (:interval "6 years 15 months 4 days 3 hours 2 minutes 1 second")))
:single)
3.0d0
Justify Days and Hours
You can adjust a period of days to be 30 day months and adjust a period
of hours to be 24 hour days. For example:
(query (:select (:interval "47 days 3 hours 2 minutes 1 second"))
:single)
((:MONTHS 0) (:DAYS 47) (:SECONDS 10921) (:USECONDS 0))
(query (:select (:justify-days (:interval "47 days 3 hours 2 minutes 1 second")))
:single)
((:MONTHS 1) (:DAYS 17) (:SECONDS 10921) (:USECONDS 0))