My query is something like...
SELECT * FROM table WHERE datetime BETWEEN two_sundays_ago AND last_sunday
I've looked through the date/time functions from IBM (link) plus some other documentation but I'm struggling to understand how to do two things.
First is how to adjust for timezone difference (subtract 6 from the hour value) while still keeping the rest of the datetime value. The datetime type is DATETIME YEAR TO FRACTION (3).
Second is how to get the two date values, two_sundays_ago and last_sunday. Example, if today is Monday 11/28/16, I'd like the query to select values between 11/20/16 to 11/27/16.
I was doing this in BIDS with the expression =DateAdd("h",6,DateAdd("d",-Weekday(Now())-6,Today)) for two_sundays_ago, but in this query I'm summarizing the values, so it's my understanding that I can't select the datetime value to filter with later on.
I think that you benefit from having a function that implements a variant of MOD where variant_MOD(i, j) returns a value in the range 1..j instead of 0..j-1 — when the value from MOD(i, j) would be 0, it returns j instead. Ignoring negative values, this jmod() does the job:
CREATE FUNCTION jmod(n INTEGER, m INTEGER) RETURNING INTEGER AS v;
LET n = MOD(n, m);
IF (n = 0) THEN LET n = m; END IF;
RETURN n;
END FUNCTION;
With this function available, and an 'operational' table t containing the date/time values recorded in UTC:
CREATE TABLE t (dt DATETIME YEAR TO FRACTION(3));
and a separate table ref_date containing a single column ref_date for the reference date, and a single row containing, for example, today's date:
CREATE TABLE ref_date (ref_date DATE NOT NULL UNIQUE);
INSERT INTO ref_date VALUES(TODAY);
then you can run a query like this to select the relevant rows in US/Central time (UTC-06:00 at the moment):
SELECT dt_utc,
dt_central,
rd.ref_date - jmod(WEEKDAY(rd.ref_date), 7) - 7 AS two_sunday_ago,
rd.ref_date - jmod(WEEKDAY(rd.ref_date), 7) AS one_sunday_ago
FROM (SELECT dt AS dt_utc, dt - INTERVAL(6:00) HOUR TO MINUTE AS dt_central FROM t) AS mapped_time
JOIN ref_date AS rd ON 1 = 1
WHERE dt_central >= (rd.ref_date - jmod(WEEKDAY(rd.ref_date), 7) - 7)
AND dt_central < (rd.ref_date - jmod(WEEKDAY(rd.ref_date), 7))
ORDER BY dt_central
;
That creates a Cartesian product of the two tables with the ON 1 = 1 condition. In Informix 12.10, you can use CROSS JOIN instead:
SELECT dt_utc,
dt_central,
rd.ref_date - jmod(WEEKDAY(rd.ref_date), 7) - 7 AS two_sunday_ago,
rd.ref_date - jmod(WEEKDAY(rd.ref_date), 7) AS one_sunday_ago
FROM (SELECT dt AS dt_utc, dt - INTERVAL(6:00) HOUR TO MINUTE AS dt_central FROM t) AS mapped_time
CROSS JOIN ref_date AS rd
WHERE dt_central >= (rd.ref_date - jmod(WEEKDAY(rd.ref_date), 7) - 7)
AND dt_central < (rd.ref_date - jmod(WEEKDAY(rd.ref_date), 7))
ORDER BY dt_central
;
I'm not sure when CROSS JOIN was added — try it; it'll probably work for you.
Given some partially random, partially systematic date like:
2016-11-01 06:07:02.029
2016-11-01 11:19:47.027
2016-11-01 14:08:31.677
2016-11-01 16:40:23.650
2016-11-01 17:47:37.206
2016-11-01 17:55:31.371
2016-11-02 10:42:05.401
2016-11-02 10:48:21.017
2016-11-02 18:27:41.661
2016-11-02 23:09:18.752
2016-11-02 23:50:20.358
2016-11-03 03:36:08.826
2016-11-03 06:07:01.217
2016-11-03 14:19:57.977
2016-11-03 16:03:01.813
2016-11-04 00:24:43.164
2016-11-04 00:58:09.912
2016-11-04 07:37:17.457
2016-11-04 07:52:10.984
2016-11-04 11:36:59.265
2016-11-04 16:44:17.827
2016-11-05 13:35:50.577
2016-11-05 15:12:14.804
2016-11-05 15:30:10.579
2016-11-05 15:45:05.364
2016-11-05 19:15:30.116
2016-11-05 22:25:58.315
2016-11-06 05:25:46.535
2016-11-06 05:32:00.865
2016-11-06 10:35:07.787
2016-11-06 20:28:40.664
2016-11-06 20:57:01.862
2016-11-07 01:31:02.657
2016-11-07 03:17:15.625
2016-11-07 06:54:13.873
2016-11-07 07:19:33.032
2016-11-07 09:12:31.531
2016-11-07 12:53:07.034
2016-11-07 17:14:34.281
2016-11-08 03:53:15.886
2016-11-08 06:20:39.009
2016-11-08 10:30:20.182
2016-11-08 14:25:14.173
2016-11-08 16:34:39.781
2016-11-08 17:59:40.327
2016-11-09 01:45:40.142
2016-11-09 06:12:05.475
2016-11-09 18:10:02.483
2016-11-09 18:29:01.374
2016-11-10 03:15:42.938
2016-11-10 14:56:39.354
2016-11-10 15:19:28.320
2016-11-10 17:05:13.327
2016-11-10 19:21:54.386
2016-11-10 21:46:10.826
2016-11-11 05:36:52.942
2016-11-11 08:35:54.840
2016-11-11 12:38:57.564
2016-11-11 18:05:08.286
2016-11-11 18:11:33.612
2016-11-11 23:22:55.222
2016-11-12 01:40:20.976
2016-11-12 07:42:37.907
2016-11-12 12:12:51.797
2016-11-12 12:34:56.000
2016-11-12 23:11:16.077
2016-11-13 02:37:34.081
2016-11-13 05:35:05.835
2016-11-13 05:54:12.853
2016-11-13 07:31:33.380
2016-11-13 11:34:56.000
2016-11-13 15:31:13.543
2016-11-13 20:20:31.259
2016-11-13 21:52:01.573
2016-11-14 02:00:39.716
2016-11-14 02:42:48.699
2016-11-14 04:31:23.312
2016-11-14 09:24:15.559
2016-11-14 10:34:56.000
2016-11-14 12:45:36.275
2016-11-14 22:55:42.745
2016-11-15 01:47:49.193
2016-11-15 13:31:26.524
2016-11-15 15:30:24.267
2016-11-15 19:07:17.032
2016-11-15 19:09:01.029
2016-11-15 19:34:56.000
2016-11-15 20:14:28.359
2016-11-16 00:01:43.336
2016-11-16 08:53:21.018
2016-11-16 09:06:47.542
2016-11-16 18:34:56.000
2016-11-16 19:45:41.016
2016-11-16 21:57:11.321
2016-11-16 23:18:24.716
2016-11-17 03:33:05.719
2016-11-17 07:01:09.417
2016-11-17 08:08:16.121
2016-11-17 09:41:19.698
2016-11-17 10:42:33.239
2016-11-17 12:15:27.553
2016-11-17 17:34:56.000
2016-11-17 21:53:55.909
2016-11-18 04:24:15.725
2016-11-18 04:46:03.008
2016-11-18 05:55:30.590
2016-11-18 12:14:24.298
2016-11-18 12:58:37.578
2016-11-18 15:49:14.780
2016-11-18 16:34:56.000
2016-11-19 05:02:52.783
2016-11-19 07:30:51.461
2016-11-19 09:56:43.041
2016-11-19 15:34:56.000
2016-11-19 18:16:47.583
2016-11-19 18:50:38.236
2016-11-20 02:38:04.760
2016-11-20 03:36:04.681
2016-11-20 05:59:59.999
2016-11-20 06:00:00.000
2016-11-20 09:14:10.393
2016-11-20 09:43:03.942
2016-11-20 11:49:54.133
2016-11-20 14:34:56.000
2016-11-20 16:26:38.713
2016-11-21 03:35:07.572
2016-11-21 13:34:56.000
2016-11-21 20:37:13.670
2016-11-21 21:50:15.654
2016-11-21 23:45:38.274
2016-11-22 05:39:06.759
2016-11-22 07:41:51.497
2016-11-22 09:15:53.879
2016-11-22 12:34:56.000
2016-11-22 15:23:12.384
2016-11-22 20:22:38.939
2016-11-22 21:50:18.608
2016-11-22 22:44:44.659
2016-11-23 03:35:36.576
2016-11-23 10:25:07.755
2016-11-23 11:34:56.000
2016-11-23 13:18:18.491
2016-11-23 18:06:22.202
2016-11-24 01:15:31.356
2016-11-24 02:28:40.031
2016-11-24 10:34:56.000
2016-11-24 12:17:26.300
2016-11-24 15:39:32.904
2016-11-24 16:50:10.130
2016-11-24 22:41:28.450
2016-11-24 22:58:19.290
2016-11-25 01:43:38.514
2016-11-25 05:55:49.725
2016-11-25 19:34:56.000
2016-11-25 21:23:51.303
2016-11-25 23:15:01.760
2016-11-26 01:30:18.578
2016-11-26 09:04:10.808
2016-11-26 09:40:47.942
2016-11-26 14:51:15.283
2016-11-26 17:45:58.383
2016-11-26 18:34:56.000
2016-11-27 00:00:00.000
2016-11-27 05:59:59.999
2016-11-27 06:00:00.000
2016-11-27 07:34:30.816
2016-11-27 08:38:15.413
2016-11-27 16:05:01.385
2016-11-27 17:34:56.000
2016-11-28 00:42:58.846
2016-11-28 05:12:39.664
2016-11-28 09:38:25.615
2016-11-28 12:47:44.086
2016-11-28 15:32:46.675
2016-11-28 16:34:56.000
2016-11-28 20:02:04.530
2016-11-28 20:06:25.688
2016-11-29 07:37:28.761
2016-11-29 14:29:18.228
2016-11-29 15:34:56.000
2016-11-29 17:54:28.873
2016-11-29 17:54:36.695
2016-11-29 20:31:31.838
2016-11-30 04:27:36.828
2016-11-30 09:08:41.064
2016-11-30 11:30:09.853
2016-11-30 12:40:14.306
2016-11-30 14:34:56.000
2016-11-30 18:55:15.446
2016-11-30 19:53:16.446
2016-11-30 19:53:56.432
and setting the reference date as 2016-11-28, the output of the query is:
dt_utc|dt_central|two_sunday_ago|one_sunday_ago
DATETIME YEAR TO FRACTION(3)|DATETIME YEAR TO FRACTION(3)|DATE|DATE
2016-11-20 06:00:00.000|2016-11-20 00:00:00.000|2016-11-20|2016-11-27
2016-11-20 09:14:10.393|2016-11-20 03:14:10.393|2016-11-20|2016-11-27
2016-11-20 09:43:03.942|2016-11-20 03:43:03.942|2016-11-20|2016-11-27
2016-11-20 11:49:54.133|2016-11-20 05:49:54.133|2016-11-20|2016-11-27
2016-11-20 14:34:56.000|2016-11-20 08:34:56.000|2016-11-20|2016-11-27
2016-11-20 16:26:38.713|2016-11-20 10:26:38.713|2016-11-20|2016-11-27
2016-11-21 03:35:07.572|2016-11-20 21:35:07.572|2016-11-20|2016-11-27
2016-11-21 13:34:56.000|2016-11-21 07:34:56.000|2016-11-20|2016-11-27
2016-11-21 20:37:13.670|2016-11-21 14:37:13.670|2016-11-20|2016-11-27
2016-11-21 21:50:15.654|2016-11-21 15:50:15.654|2016-11-20|2016-11-27
2016-11-21 23:45:38.274|2016-11-21 17:45:38.274|2016-11-20|2016-11-27
2016-11-22 05:39:06.759|2016-11-21 23:39:06.759|2016-11-20|2016-11-27
2016-11-22 07:41:51.497|2016-11-22 01:41:51.497|2016-11-20|2016-11-27
2016-11-22 09:15:53.879|2016-11-22 03:15:53.879|2016-11-20|2016-11-27
2016-11-22 12:34:56.000|2016-11-22 06:34:56.000|2016-11-20|2016-11-27
2016-11-22 15:23:12.384|2016-11-22 09:23:12.384|2016-11-20|2016-11-27
2016-11-22 20:22:38.939|2016-11-22 14:22:38.939|2016-11-20|2016-11-27
2016-11-22 21:50:18.608|2016-11-22 15:50:18.608|2016-11-20|2016-11-27
2016-11-22 22:44:44.659|2016-11-22 16:44:44.659|2016-11-20|2016-11-27
2016-11-23 03:35:36.576|2016-11-22 21:35:36.576|2016-11-20|2016-11-27
2016-11-23 10:25:07.755|2016-11-23 04:25:07.755|2016-11-20|2016-11-27
2016-11-23 11:34:56.000|2016-11-23 05:34:56.000|2016-11-20|2016-11-27
2016-11-23 13:18:18.491|2016-11-23 07:18:18.491|2016-11-20|2016-11-27
2016-11-23 18:06:22.202|2016-11-23 12:06:22.202|2016-11-20|2016-11-27
2016-11-24 01:15:31.356|2016-11-23 19:15:31.356|2016-11-20|2016-11-27
2016-11-24 02:28:40.031|2016-11-23 20:28:40.031|2016-11-20|2016-11-27
2016-11-24 10:34:56.000|2016-11-24 04:34:56.000|2016-11-20|2016-11-27
2016-11-24 12:17:26.300|2016-11-24 06:17:26.300|2016-11-20|2016-11-27
2016-11-24 15:39:32.904|2016-11-24 09:39:32.904|2016-11-20|2016-11-27
2016-11-24 16:50:10.130|2016-11-24 10:50:10.130|2016-11-20|2016-11-27
2016-11-24 22:41:28.450|2016-11-24 16:41:28.450|2016-11-20|2016-11-27
2016-11-24 22:58:19.290|2016-11-24 16:58:19.290|2016-11-20|2016-11-27
2016-11-25 01:43:38.514|2016-11-24 19:43:38.514|2016-11-20|2016-11-27
2016-11-25 05:55:49.725|2016-11-24 23:55:49.725|2016-11-20|2016-11-27
2016-11-25 19:34:56.000|2016-11-25 13:34:56.000|2016-11-20|2016-11-27
2016-11-25 21:23:51.303|2016-11-25 15:23:51.303|2016-11-20|2016-11-27
2016-11-25 23:15:01.760|2016-11-25 17:15:01.760|2016-11-20|2016-11-27
2016-11-26 01:30:18.578|2016-11-25 19:30:18.578|2016-11-20|2016-11-27
2016-11-26 09:04:10.808|2016-11-26 03:04:10.808|2016-11-20|2016-11-27
2016-11-26 09:40:47.942|2016-11-26 03:40:47.942|2016-11-20|2016-11-27
2016-11-26 14:51:15.283|2016-11-26 08:51:15.283|2016-11-20|2016-11-27
2016-11-26 17:45:58.383|2016-11-26 11:45:58.383|2016-11-20|2016-11-27
2016-11-26 18:34:56.000|2016-11-26 12:34:56.000|2016-11-20|2016-11-27
2016-11-27 00:00:00.000|2016-11-26 18:00:00.000|2016-11-20|2016-11-27
2016-11-27 05:59:59.999|2016-11-26 23:59:59.999|2016-11-20|2016-11-27
You can see precise cut-offs in the data; the times 05:59:59.999 and 06:00:00.000 appear in the correct places (and those rows are not present by random chance).
Given a reference date of 2016-11-27, the output is:
dt_utc|dt_central|two_sunday_ago|one_sunday_ago
DATETIME YEAR TO FRACTION(3)|DATETIME YEAR TO FRACTION(3)|DATE|DATE
2016-11-13 07:31:33.380|2016-11-13 01:31:33.380|2016-11-13|2016-11-20
2016-11-13 11:34:56.000|2016-11-13 05:34:56.000|2016-11-13|2016-11-20
2016-11-13 15:31:13.543|2016-11-13 09:31:13.543|2016-11-13|2016-11-20
2016-11-13 20:20:31.259|2016-11-13 14:20:31.259|2016-11-13|2016-11-20
2016-11-13 21:52:01.573|2016-11-13 15:52:01.573|2016-11-13|2016-11-20
2016-11-14 02:00:39.716|2016-11-13 20:00:39.716|2016-11-13|2016-11-20
2016-11-14 02:42:48.699|2016-11-13 20:42:48.699|2016-11-13|2016-11-20
2016-11-14 04:31:23.312|2016-11-13 22:31:23.312|2016-11-13|2016-11-20
2016-11-14 09:24:15.559|2016-11-14 03:24:15.559|2016-11-13|2016-11-20
2016-11-14 10:34:56.000|2016-11-14 04:34:56.000|2016-11-13|2016-11-20
2016-11-14 12:45:36.275|2016-11-14 06:45:36.275|2016-11-13|2016-11-20
2016-11-14 22:55:42.745|2016-11-14 16:55:42.745|2016-11-13|2016-11-20
2016-11-15 01:47:49.193|2016-11-14 19:47:49.193|2016-11-13|2016-11-20
2016-11-15 13:31:26.524|2016-11-15 07:31:26.524|2016-11-13|2016-11-20
2016-11-15 15:30:24.267|2016-11-15 09:30:24.267|2016-11-13|2016-11-20
2016-11-15 19:07:17.032|2016-11-15 13:07:17.032|2016-11-13|2016-11-20
2016-11-15 19:09:01.029|2016-11-15 13:09:01.029|2016-11-13|2016-11-20
2016-11-15 19:34:56.000|2016-11-15 13:34:56.000|2016-11-13|2016-11-20
2016-11-15 20:14:28.359|2016-11-15 14:14:28.359|2016-11-13|2016-11-20
2016-11-16 00:01:43.336|2016-11-15 18:01:43.336|2016-11-13|2016-11-20
2016-11-16 08:53:21.018|2016-11-16 02:53:21.018|2016-11-13|2016-11-20
2016-11-16 09:06:47.542|2016-11-16 03:06:47.542|2016-11-13|2016-11-20
2016-11-16 18:34:56.000|2016-11-16 12:34:56.000|2016-11-13|2016-11-20
2016-11-16 19:45:41.016|2016-11-16 13:45:41.016|2016-11-13|2016-11-20
2016-11-16 21:57:11.321|2016-11-16 15:57:11.321|2016-11-13|2016-11-20
2016-11-16 23:18:24.716|2016-11-16 17:18:24.716|2016-11-13|2016-11-20
2016-11-17 03:33:05.719|2016-11-16 21:33:05.719|2016-11-13|2016-11-20
2016-11-17 07:01:09.417|2016-11-17 01:01:09.417|2016-11-13|2016-11-20
2016-11-17 08:08:16.121|2016-11-17 02:08:16.121|2016-11-13|2016-11-20
2016-11-17 09:41:19.698|2016-11-17 03:41:19.698|2016-11-13|2016-11-20
2016-11-17 10:42:33.239|2016-11-17 04:42:33.239|2016-11-13|2016-11-20
2016-11-17 12:15:27.553|2016-11-17 06:15:27.553|2016-11-13|2016-11-20
2016-11-17 17:34:56.000|2016-11-17 11:34:56.000|2016-11-13|2016-11-20
2016-11-17 21:53:55.909|2016-11-17 15:53:55.909|2016-11-13|2016-11-20
2016-11-18 04:24:15.725|2016-11-17 22:24:15.725|2016-11-13|2016-11-20
2016-11-18 04:46:03.008|2016-11-17 22:46:03.008|2016-11-13|2016-11-20
2016-11-18 05:55:30.590|2016-11-17 23:55:30.590|2016-11-13|2016-11-20
2016-11-18 12:14:24.298|2016-11-18 06:14:24.298|2016-11-13|2016-11-20
2016-11-18 12:58:37.578|2016-11-18 06:58:37.578|2016-11-13|2016-11-20
2016-11-18 15:49:14.780|2016-11-18 09:49:14.780|2016-11-13|2016-11-20
2016-11-18 16:34:56.000|2016-11-18 10:34:56.000|2016-11-13|2016-11-20
2016-11-19 05:02:52.783|2016-11-18 23:02:52.783|2016-11-13|2016-11-20
2016-11-19 07:30:51.461|2016-11-19 01:30:51.461|2016-11-13|2016-11-20
2016-11-19 09:56:43.041|2016-11-19 03:56:43.041|2016-11-13|2016-11-20
2016-11-19 15:34:56.000|2016-11-19 09:34:56.000|2016-11-13|2016-11-20
2016-11-19 18:16:47.583|2016-11-19 12:16:47.583|2016-11-13|2016-11-20
2016-11-19 18:50:38.236|2016-11-19 12:50:38.236|2016-11-13|2016-11-20
2016-11-20 02:38:04.760|2016-11-19 20:38:04.760|2016-11-13|2016-11-20
2016-11-20 03:36:04.681|2016-11-19 21:36:04.681|2016-11-13|2016-11-20
2016-11-20 05:59:59.999|2016-11-19 23:59:59.999|2016-11-13|2016-11-20