SQLite and Dates

Sometimes we want to store the date/time as an integer. It makes sense sometimes. If you want to run updates on everything higher than a certain number, you can do it quite easily with an integer. If you want to run everything all over again, compare it to zero. All records will be processed again. Easy peasy.

Here’s how one of my tables looks to achieve this integer method for a date/time:

CREATE TABLE notes(
    id INTEGER primary key, 
    title TEXT, 
    note_txt TEXT, 
    created_dt INTEGER not null default (strftime('%s', 'now', 'localtime')),
    exported_flag INTEGER not null default 0
);

See how that works? Yeah, it’s pretty neat if I do say so myself. Of course you have to convert it to make it human readable. To do that, I usually do something like this:

SELECT id, title, note_txt, created_dt, 
    strftime('%Y-%m-%d %H:%M', created_dt) as formatted_dt 
FROM notes 
ORDER BY id DESC 
LIMIT 1

But, I can see what you’re thinking. Why do it that way at all? Aside from the small example I gave, I really don’t see a good reason to store it that way. I’m sure there’s a reason for it. I just don’t know what that reason is. I’m sure someday I will figure it out.

Until then, sometimes I use it, other times I don’t. I guess it all depends on how I’m feeling that day.

Until next time.