ion "car",
"bike", etc...?
ADD: Consider to do more complex "detailed" SELECTs that are unioned (if
that is really needed)?
--
regards, marian wendt
1, 10);
ALTER TABLE cars ADD primary key (id);
CREATE INDEX ON cars(dealer_name);
ANALYZE;
--
- Lauri
With an INNER JOIN, both tables must be fully checked/matched (check
using EXPLAIN ANALYSIS -> HashJoin), so the index cannot be used here.
Sorry, didn't consider the WITH part. Please share the detailed query
plan for more info.
--
regards, marian wendt
1, 10);
ALTER TABLE cars ADD primary key (id);
CREATE INDEX ON cars(dealer_name);
ANALYZE;
--
- Lauri
With an INNER JOIN, both tables must be fully checked/matched (check
using EXPLAIN ANALYSIS -> HashJoin), so the index cannot be used here.
--
regards, marian wendt
S sub
WHERE
sub.gs >= '2023-11-05 00:00:00 America/New_York'::timestamptz AND
sub.gs < '2023-11-06 00:00:00 America/New_York'::timestamptz
--
regards, marian wendt
stamps as a linear quantity in the db
(like Steve wrote: "point in time"); no gaps, no duplications.
For the sake of simplicity, I only included the possible date-bin
variants so that the results can be compared; ordering and grouping with
just one date_bin etc. can be easily customized...
SET TIMEZONE='Etc/UTC';
SELECT
sub.gs
,date_bin('15 minutes', sub.gs, '2023-01-01') AS norm15minutes
,date_bin('1 hours', sub.gs, '2023-01-01') AS norm1hour
,date_bin('1 days', sub.gs, '2023-01-01') AS norm1day
FROM (SELECT generate_series('2023-11-03 00:00:00Z'::timestamptz,
'2023-11-06 00:00:00Z'::timestamptz, '5 minutes'::interval) AS gs) AS sub;
For the WHERE clause also everything in UTC (the conversion of the
parameters of "user time zone" takes place before).
Conversion of the results into the "user time zone" takes place in the
client app.
--
regards, marian wendt
