I have the following query:
<code>SELECT DISTINCT date_format(timestamp, '%Y-%m-%dT%H:%i:00.000Z') as times
FROM "mytable"
WHERE year = {{.Year}}
AND month = {{.Month}}
AND (day = {{.Day}} OR day = {{.Day}} + 1)
AND timestamp >= TIMESTAMP '{{.Start}}'
ORDER BY 1
</code>
<code>SELECT DISTINCT date_format(timestamp, '%Y-%m-%dT%H:%i:00.000Z') as times
FROM "mytable"
WHERE year = {{.Year}}
AND month = {{.Month}}
AND (day = {{.Day}} OR day = {{.Day}} + 1)
AND timestamp >= TIMESTAMP '{{.Start}}'
ORDER BY 1
</code>
SELECT DISTINCT date_format(timestamp, '%Y-%m-%dT%H:%i:00.000Z') as times
FROM "mytable"
WHERE year = {{.Year}}
AND month = {{.Month}}
AND (day = {{.Day}} OR day = {{.Day}} + 1)
AND timestamp >= TIMESTAMP '{{.Start}}'
ORDER BY 1
However, I believe this query would break at the end of the month, plus there are months where February ends on 28th or 29th. What would be the correct query for the day portion of the query?
Also, we need to include the case where the two days overlap between the month and the year.
4
I’d do something like this:
<code>SELECT DISTINCT date_format(timestamp, '%Y-%m-%dT%H:%i:00.000Z') as times
FROM "mytable"
WHERE make_date(year, month, day) between
make_date({{.Year}}, {{.Month}}, {{.Day}}) and
make_date({{.Year}}, {{.Month}}, {{.Day}})) + interval '1 day'
AND timestamp >= TIMESTAMP '{{.Start}}'
ORDER BY 1
</code>
<code>SELECT DISTINCT date_format(timestamp, '%Y-%m-%dT%H:%i:00.000Z') as times
FROM "mytable"
WHERE make_date(year, month, day) between
make_date({{.Year}}, {{.Month}}, {{.Day}}) and
make_date({{.Year}}, {{.Month}}, {{.Day}})) + interval '1 day'
AND timestamp >= TIMESTAMP '{{.Start}}'
ORDER BY 1
</code>
SELECT DISTINCT date_format(timestamp, '%Y-%m-%dT%H:%i:00.000Z') as times
FROM "mytable"
WHERE make_date(year, month, day) between
make_date({{.Year}}, {{.Month}}, {{.Day}}) and
make_date({{.Year}}, {{.Month}}, {{.Day}})) + interval '1 day'
AND timestamp >= TIMESTAMP '{{.Start}}'
ORDER BY 1
3