2

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.

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

  2. 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.

esafresa
  • 480
  • 7
  • 19
  • (1) What do you mean by 'adjust for timezone difference'? Which timezone? How does the system know which timezone? The short answer is add or subtract an appropriate interval: ±INTERVAL(4:30) HOUR TO MINUTE for a delta of 4 hours, 30 minutes. (2) On Sunday 27th November, the relevant dates are Sunday 13th November and Sunday 20th November? On Monday 28th November, the relevant dates are Sunday 20th November and Sunday 27th November? And on Saturday 26th November, the relevant dates are also 13th and 20th? (3) It isn't clear what you're trying; please show some SQL for the summary stuff. – Jonathan Leffler Nov 28 '16 at 05:26
  • Jon, correct, almost everything you said is correct. I'm not sure if you're familiar with BIDS, but I'm basically trying to write the =DateAdd(etc. see post) equivalent for Informix. I'm just trying to grab information from last week based on whatever todays date is, and also adjust for timezone difference in the process – esafresa Nov 28 '16 at 17:49
  • SELECT * FROM table WHERE datetime BETWEEN (today-weekday-7) AND (today-weekday) .... datetime type is DATETIME YEAR TO FRACTION (3). And then need to adjust for timezone. The DB is in UTC and we're in Central time – esafresa Nov 28 '16 at 17:55
  • When you ask for dates between 13th and 20th November, do you want all 8 days? From 2016-11-13 00:00:00 through 2016-11-20 23:59:59.99999? Or if the current time is 10:02:39, do you want from 2016-11-13 10:02:39 to 2016-11-20 10:02:39? And if you want 'start and end of day', do you want those to be in the timezone adjusted values (so midnight in US/Central to midnight) — or midnight in UTC, or something else? – Jonathan Leffler Nov 28 '16 at 18:04
  • From 2016-11-13 00:00:00 through 2016-11-**19** 23:59:59.999 in Central time (adjusted) – esafresa Nov 28 '16 at 19:13
  • OK; `dt >= 2016-11-13 00:00:00 AND dt < 2016-11-20 00:00:00` with the inclusive (`>=`) and exclusive (`<`) boundaries — BETWEEN/AND uses inclusive boundaries for both. So, for a given reference date in Central time, default today, you need the rows from the start of two Sundays ago up to but not including the start of one Sunday ago. I think that's doable...Funnily enough, I run my DB in UTC routinely (but I'm more often interested in US/Pacific than US/Central time as the local time zone). – Jonathan Leffler Nov 28 '16 at 19:22

1 Answers1

2

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
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
  • Thanks for your help with this Jonathan. Unfortunately I don't think we can touch the DB, it's for a Cisco call center and don't want to mess with it. I'm going to try summarizing the values after the query is run instead. I learned a lot reading your script though, we may try something similar to it without creating the new table. Again, thank you! – esafresa Nov 28 '16 at 23:36
  • The `ref_date` table could be a temporary table rather than a permanent one. You could also write a cursory stored procedure that is passed the reference date and returns the correct data. There are endless ways of processing this stuff. The key items are: (1) sub-query to create Central time stamps; (2) `jmod` function (name entirely at your choosing) to calculate date offsets correctly. If the function is deemed a problem, you can probably write a case expression instead: `CASE MOD(i, j) WHEN 0 THEN j ELSE MOD(i, j) END` but it isn't as elegant and makes the code harder to understand. – Jonathan Leffler Nov 28 '16 at 23:41