JS millisecond dates and SQLite second dates, best practices?

[woodrna]

I'm using a virtual table to manipulate date information in my SQLite database. JS dates use millisecond timestamps, while SQLite uses seconds. I'm using native SQLite date functions in other queries, so just storing the milliseconds value isn't a good option.

My question is whether there is a better way to do things other than multiply all my values by 1000 on the way in and divide by 1000 on the way out. It's not like it's slow now, but it offends my sensibilities if you know what I mean.

Thanks,
Nate

[mceachen]

I totally get it.

I was super surprised to find (several years ago, but after using it for a decade) that SQLite doesn't support date or time column types at all: there are only a handful of date/time functions and those pretty much all revolve around ISO-esque strings.

I've resolved to storing millisecond timestamps for trusted times, and using something else when the timezone isn't necessarily correct.

You could adopt a naming scheme that appends "_ms" as a suffix for milliseconds-since-common-epoch values, if you think that helps system clarity.

If this sort of thing bugs you, you might be interested in knowing that SQLite is extremely lax by default. You need to specifically enable strict tables and foreign key checks, for example.

[woodrna]

Thanks for the commiseration and advice. I will take your advice and re-work my queries around storing milliseconds instead of seconds. I have around 1B records in my table, so keeping the processing efficient is high up on the list of priorities. Thanks again! Nate