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.
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))There are four different formats available for providing the interval parameter.
Choose whichever one you like:
(query (:select (:interval "1-2")) :single)((:MONTHS 14) (:DAYS 0) (:SECONDS 0) (:USECONDS 0))(query (:select (:interval "3 4:05:06")) :single)((:MONTHS 0) (:DAYS 3) (:SECONDS 14706) (:USECONDS 0))
(query (:select (:interval "1 year 2 months 3 days 4 hours 5 minutes 6 seconds")) :single)((:MONTHS 14) (:DAYS 3) (:SECONDS 14706) (:USECONDS 0))
(query (:select (:interval "P0001-02-03T04:05:06")) :single)((:MONTHS 14) (:DAYS 3) (:SECONDS 14706) (:USECONDS 0))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"
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))))
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
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))