SQLite Forum

SQL query help for between times and grouping times
It is stepwise.

src1 contains timestamps in the IOS8601 format recognized by SQLite3 (YYYY-MM-DD HH:MM:SS) for 8760 hours from now every hour for one year.  This will include all possible permutations of formatting when reconstructed into 12-hour format.

src2 computes the datepart (dp) for the timestamp in the form m/d/y with no padding, and the 24-hour part of the timestamp (th), plus the rest of the timestamp (:mm:ss) trest.

src3 uses the 24-hour number and computes the 12-hour value (nh) and the AM/PM indicator (ap).

src4 glues together the 12-hour value, the rest of the timestring, and the AM/PM indicator to form a 12-hour time string.

cvt contains the original ISO8601 timestamp, the datepart (dp) computed above, the 12-hour timepart (tp), and a new ISO8601 timestamp computed using the dp and tp.

The end select displays all the data plus an indication of whether the timestamp reconstructed from the deconstructed timestamps is the same as the original timestamp.