#+TITLE: Interval Notes
#+OPTIONS: num:nil
#+HTML_HEAD: <link rel="stylesheet" type="text/css" href="style.css" />
#+HTML_HEAD: <style>pre.src{background:#343131;color:white;} </style>
#+OPTIONS: ^:nil
* Setting the Baseline Default Reader
  :PROPERTIES:
  :ID:       c6baef62-f431-451c-b297-00db0e48b69f
  :END:
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.
#+BEGIN_SRC lisp
(setf cl-postgres:*sql-readtable*
        (cl-postgres:copy-sql-readtable
         cl-postgres::*default-sql-readtable*))
#+END_SRC
* Universal Time and Timestamp Libraries
  :PROPERTIES:
  :ID:       49a51426-a091-44f0-8248-bb1169e1f12a
  :END:

Absent anything else, a query to postgresql through postmodern will return a
universal time. E.g.
#+BEGIN_SRC lisp
(query (:select (:current-timestamp)) :single)

3743806998
#+END_SRC
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.

#+BEGIN_SRC lisp
(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>
#+END_SRC
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
  :PROPERTIES:
  :ID:       7b52f41e-83c3-4f27-859f-b2bb80a53c88
  :END:
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
   :PROPERTIES:
   :ID:       630b0d70-4ca3-41d5-848f-01cda8231d95
   :END:
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.
#+BEGIN_SRC lisp
(query (:select (:interval "6 years 5 months 4 days 3 hours 2 minutes 1 second"))
  :single)

((:MONTHS 77) (:DAYS 4) (:SECONDS 10921) (:USECONDS 0))
#+END_SRC
** Creating an interval column
#+BEGIN_SRC lisp
(query (:create-table 'data-type-tests
                      ((len :type (or interval db-null) :interval :hour-to-minute))))
#+END_SRC
** Allowable Formats of the parameter to Interval
   :PROPERTIES:
   :ID:       48cca061-c649-44e3-8312-e349de885b3f
   :END:
There are four different formats available for providing the interval parameter.
Choose whichever one you like:
*** SQL Standard Format
    :PROPERTIES:
    :ID:       b435a518-a906-4314-800c-3d18f47f8f06
    :END:
#+BEGIN_SRC lisp
(query (:select (:interval "1-2"))
  :single)

((:MONTHS 14) (:DAYS 0) (:SECONDS 0) (:USECONDS 0))
#+END_SRC
*** Traditional Postgresql Format
    :PROPERTIES:
    :ID:       b7058c5b-d0d9-40b6-8d07-2b02123068b3
    :END:
#+BEGIN_SRC lisp
(query (:select (:interval "3 4:05:06"))
  :single)

((:MONTHS 0) (:DAYS 3) (:SECONDS 14706) (:USECONDS 0))
#+END_SRC
*** ISO 8601 Format with Designators
    :PROPERTIES:
    :ID:       43a72f38-6744-48cb-9fdd-6806cea409de
    :END:
#+BEGIN_SRC lisp
(query (:select (:interval "1 year 2 months 3 days 4 hours 5 minutes 6 seconds"))
  :single)

((:MONTHS 14) (:DAYS 3) (:SECONDS 14706) (:USECONDS 0))
#+END_SRC
*** ISO 8601 Alternative Format
    :PROPERTIES:
    :ID:       14de15c4-68d8-4107-95e2-c2976344eafd
    :END:
#+BEGIN_SRC lisp
(query (:select (:interval "P0001-02-03T04:05:06"))
  :single)

((:MONTHS 14) (:DAYS 3) (:SECONDS 14706) (:USECONDS 0))
#+END_SRC

** Formatting Intervals to String
   :PROPERTIES:
   :ID:       09172b69-1881-4608-bea6-bc51dbd8c3f2
   :END:
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.
#+BEGIN_SRC
(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"
#+END_SRC
Just to be different, we can add weeks and days instead of years and months. E.g.
#+BEGIN_SRC lisp
(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"
#+END_SRC

** Math with Intervals
   :PROPERTIES:
   :ID:       ce85ec08-d1b8-49b8-9a2a-eb0a6a496ed5
   :END:
Adding or subtracting intervals from each other will result in an interval.
#+BEGIN_SRC lisp
(query (:select (:+ (:interval "2h 50min") (:interval "10min")))
  :single)

((:MONTHS 0) (:DAYS 0) (:SECONDS 10800) (:USECONDS 0))
#+END_SRC

The difference between two timestamps is always an interval. Note: you
cannot add, multiple or divide two timestamps.
#+BEGIN_SRC lisp
(query
 (:select (:- (:timestamp "1999-12-30") (:timestamp "1999-12-11")))
 :single)

((:MONTHS 0) (:DAYS 19) (:SECONDS 0) (:USECONDS 0))
#+END_SRC

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
#+BEGIN_SRC lisp
(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"
#+END_SRC
They can be cast back to the alist interval style:
#+BEGIN_SRC lisp
(query
  (:select
    (:type (:- (:timestamp "2016-12-31 03:00") (:timestamp "2016-12-29 13:00"))
           interval))
  :single)

((:MONTHS 0) (:DAYS 1) (:SECONDS 50400) (:USECONDS
#+END_SRC
Or you can use local-time or simple-date to convert the result into some
type of timestamp.
#+BEGIN_SRC lisp
(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>
#+END_SRC

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.
#+BEGIN_SRC lisp
(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))))
#+END_SRC

** Extracting Subparts
   :PROPERTIES:
   :ID:       38666c25-b699-4a8a-8db7-88472b13bc0c
   :END:
You can extract a part of an interval using :extract.
#+BEGIN_SRC lisp
(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
#+END_SRC
** Justify Days and Hours
   :PROPERTIES:
   :ID:       2ad994c7-2167-41f9-8c5e-6cbc9695e912
   :END:
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:

#+BEGIN_SRC
(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))
#+END_SRC