Interval Notes

Setting the Baseline Default Reader

Just to ensure that we have the same baseline readtable, we are going to make sure that the cl-postgres:*sql-readtable* is set to the default.

(setf cl-postgres:*sql-readtable*
        (cl-postgres:copy-sql-readtable
         cl-postgres::*default-sql-readtable*))

Universal Time and Timestamp Libraries

Absent anything else, a query to postgresql through postmodern will return a universal time. E.g.

(query (:select (:current-timestamp)) :single)

3743806998

To get a timestamp, you could use the local-time library or the simple-date library. Just for comparison sake, we will use both and get the universal time from both the system and from postgresql.

(local-time:universal-to-timestamp
  (query (:select (:current-timestamp))
    :single))

@2018-08-20T19:23:34.000000-07:00

(local-time:universal-to-timestamp (get-universal-time))

@2018-08-20T19:23:42.000000-07:00

(simple-date:universal-time-to-timestamp (get-universal-time))

#<SIMPLE-DATE:TIMESTAMP 21-08-2018T02:26:00>

(simple-date:universal-time-to-timestamp
  (query (:select (:current-timestamp))
    :single))

#<SIMPLE-DATE:TIMESTAMP 21-08-2018T02:26:18>

Notice something interesting about the simple-date timestamp compared to the local-time timestamp? The local-time timestamp is (at least on the machine I am using as I write this and the postgresql server it is connected to) showing a Pacific Daylight time, 7 hours earlier than GMT. The simple-date timestamp is showing the GMT time. If you care about timezones, you will need to pay attention to where your server is, where the clients are and what do you want to be tracking or calculating to.

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