This is a card in Dave's Virtual Box of Cards.

SQLite - selecting today's entries from a series of dates

Page created: 2024-11-27

Updated: 2025-03-29

Back to sqlite.

I’m storing entries with dates as unix epoch seconds (wikipedia.org).

My table and how I’m storing "start" timestamps:

CREATE TABLE times (user, type, start, end);
INSERT INTO times (user, type, start) VALUES (1234, 1, unixepoch('now'));

Storing as UTC timestamps

Storing UTC timestamps is okay, but know that it "loses" information about a person’s local time when the entry was made.

In other words, The One True Time of, say 02:00:00 has been recorded, but you have no way of knowing that the clock on the wall in the person’s house said 5 O’Clock because of the time zone and season (with or without DST).

If you do need to know this, store the date WITH a timezone (which implies a whole almanac of historical and projected future seasonal time changes).

But you do not need to know the timezone of the originating post to know the absolute time in which it was posted. Nor do you need to store it to adjust the "day" of entries for a user in a specific timezone.

What, even, is a day?

Before we go any further, let’s contemplate the absurdity of even trying to get a "day" worth of information for two people posting to the same application from different parts of the planet.

Person A posts something at night in Brazil. Person B posts something in the early afternoon in Japan. It’s the exact same time, but two different days.

What is a shared meaning of "today" between these two people?

I don’t know and neither do you. (I suspect it depends on the situation, so that there is no one correct answer. "Your mileage will vary.")

Getting entries for local "today" correctly and simply

(There’s a crazy, ranting subtitle below this section. That was my first stab at this problem. Somehow, it was still wrong. I’m sure it could be made correct, and believe me, I’ve tried. But the solution in this section is so simple, it makes me laugh with joy.)

One thing remains true: No matter what solution I use, the client application requesting "today’s" entries will have to supply the local time offset. That’s unavoidable.

So it finally dawned on me that I can simplifiy the heck out of my queries by having the client figure out the range of timestamps that would constitute a local "day".

My client is in JS, so I can use the arcane date/time functions available to get my UTC timestamps for a local "today" in UTC timestamps like so:

// Beteen 00:00 today and 00:00 tomorrow
var d1 = new Date(); d1.setHours(0,0,0,0);
var d2 = new Date(); d2.setHours(24,0,0,0);
// JS stores 'em in milliseconds, so divide by 1000
var my_utc_day_start = d1 / 1000;
var my_utc_day_end = d2 / 1000;

Then on the server I can request entries with this simple range:

SELECT * FROM times
WHERE start BETWEEN 1732942800 AND 1733029200;

That’s it. A "day’s" worth of entries for a user’s local time.

I recommend not reading past this point. You probably don’t want any of this:

DO NOT DO IT THIS WAY - TIMEZONE CONVERSIONS MAKE MY BRAIN POP OUT OF MY SKULL AND THIS IS WHY WE CAN’T HAVE NICE THINGS

I want to get today’s entries. This would work:

SELECT * FROM times WHERE date(start) = date('now');

But I believe this will force SQLite to convert every start field in the whole table from a unixepoch to a date before doing a comparison.

Instead, I think it would be far more efficient to compare apples with apples to avoid the comparison.

I’ll do a little experiment:

SELECT unixepoch('now');                 -- 1732730595

SELECT unixepoch(date('now', '-1 day')); -- 1732579200
SELECT unixepoch(date('now'));           -- 1732665600
SELECT unixepoch(date('now', '+1 day')); -- 1732752000

Okay, so I can see that the exact timestamp for 'now' (1732730595) can be found between the start-of-day for 'now' (1732665600) and the start-of-day for 'now +1 day' (tomorrow, 1732752000). So I conclude that the correct select for today using timestamps is:

SELECT date('now'), datetime(start, 'unixepoch') FROM times
WHERE start BETWEEN unixepoch(date('now'))
                AND unixepoch(date('now', '+1 day'));

-- Today      | Entry datetime
-- -----------+--------------------
-- 2024-11-27 | 2024-11-27 18:40:47
-- 2024-11-27 | 2024-11-27 18:40:47

I don’t have enough data in this DB to benchmark this, but it looks good to me.

Timezone Offset

Oh boy, and then a matter of hours later, my query started coming up empty. Of course! My server is storing dates properly in UTC…where it’s now very early in the next day.

Crud. Okay, so when requesting "today", I’m going to need to offset my requests in accordance with my local timezone. I don’t want to just hard-code for a couple reasons - the most important being the seasonal "daylight saving time" shift for those places where this barbaric and insane practice is still performed. (This is why we can’t have nice things.)

My browser front-end will need to get the local offset for the current season and pass it with every request.

Thankfully, this is really easy in JavaScript:

var offset_min = (new Date()).getTimezoneOffset()

Note that, you always subtract the value returned by getTimezoneOffset() to get the localized time. (The positive and negatives are the opposite of what you might expect.)

In my case, offset_min is 300 minutes (timezone offset UTC-05:00).

Also, offsets aren’t always whole hour increments. So we really do need minutes. (This is why we can’t have nice things.)

I need to offset both the "start-of-today" and the "start-of-tomorrow" by adding the minute offset to 'now' and changing +1 day to one day minus the offset minutes:

(24 * 60) - 300 = 1140

SELECT date('now'), datetime(start, 'unixepoch') FROM times
WHERE start BETWEEN unixepoch(date('now', '-300 minutes'))
                AND unixepoch(date('now', '+1140 minutes'));

-- Today      | Entry datetime
-- -----------+--------------------
-- 2024-11-28 | 2024-11-27 18:40:47
-- 2024-11-28 | 2024-11-27 18:40:47

Note that the "Today" column and "Entry datetime" no longer seem to line up, but that’s just a disagreement about "now". The datetimes in the entries are still in UTC and will need to be converted to local time. I could do this in my backend code or on the frontend in the browser, but I think it makes the most sense to have SQLite calculate this too:

SELECT date('now', '-300 minutes'),
       datetime(start, 'unixepoch', '-300 minutes')
 FROM times
WHERE start BETWEEN unixepoch(date('now', '-300 minutes'))
                AND unixepoch(date('now', '+1140 minutes'));

-- Today      | Entry datetime
-- -----------+--------------------
-- 2024-11-27 | 2024-11-27 13:40:47
-- 2024-11-27 | 2024-11-27 13:40:47

The query has certainly grown, but now everything corresponds with my local time. Whew.

(This is why we can’t have nice things.)