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))