8 ms·

I find this page to be somewhat vague. For example, any data that revolves around dates or time may require some addition hoops to jump through since, as https

I find this page to be somewhat vague. For example, any data that revolves around dates or time may require some addition hoops to jump through since, as https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html points out, "SQLite does not have a storage class set aside for storing dates and/or times." As much as I love SQLite, I find this little issue always adding an extra bit of work to deal with. Not a blocker, but useful to know. I've found that in some (rare) cases, H2 became a better solution (horrendous start time, slow data load, but ability to use real date and time types reduced errors and improved analyses). Finally, knowing that SQLite is not right for a situation is great, but it wouldn't hurt for them to mention a potential alternative, if the devs or community know of one.

le-mark 9y ago
That's a valid observation, but the status of sqlite as a couple of ansi c file you can compile with or link to any damn thing is really what we're talking about here. And H2, awesome as it is, is no comparison.
mikeash 9y ago
I don’t know if they want to be in the business of recommending alternatives. Doing so has the unfortunate tendency of making people think it’s your problem if you use them and they don’t work out.
kragen 9y ago
For others who didn't know about H2, it's apparently an embeddable SQL database, like SQLite, but in Java: https://github.com/h2database/h2database https://github.com/h2database/h2database It's dual-licensed under the Mozilla Public License and the Eclipse Public License.
- hinkley 9y ago
  It grew up arm in arm with Hibernate, one of the better ORM wrappers. Fast enough for local use that you could do functional tests against your domain objects without using mocks, or crying.
coleifer 9y ago
The lack of a datetime data-type hasn't been a problem in my experience. I have, at different times, stored the timestamp as an integer representing number of microseconds since the epoch, or stored in YYYY-mm-dd HH:MM:SS (which sorts lexicographically so comparisons come out right). SQLite has some built-in facilities for working with datetimes as well, along the lines of strftime, etc. H2, being java, is in a whole different universe from SQLite...not a valid comparison in my view.
- mulmen 9y ago
  ...in UTC. Right? RIGHT?!
  - HumanDrivenDev 9y ago
    Not going to lie, after reading that I quickly went to check that "Date.now()" in javascript returned UTC.
oneweekwonder 9y ago
Not as lightweight as sqllite. But I always wonder why firebird embedded-able server[0] does not get more love? The 32-bit so library just under 7MB and then you have a full rdbms available to your application. [0] https://www.firebirdsql.org/pdfmanual/html/fbmetasecur-embedded.html https://www.firebirdsql.org/pdfmanual/html/fbmetasecur-embed...
- lwf 9y ago
  Well, for one, SQLite's 32-bit dll is only 858KiB :)
  - oneweekwonder 9y ago
    Feature richness comparable to oracle, mysql, postgres, comes at a price. Another perspective; embeddable py 10MB, electron app 50MB[0], free pascal exe 1.5MB. So 7MB for a RDBMS seems reasonable, but not comparable to SQLite as you pointed out. [0]: https://github.com/electron/electron/issues/2003#issuecomment-135592382 https://github.com/electron/electron/issues/2003#issuecommen...
- jug 9y ago
  Poorly advertised? With a lack of marketing/funding I can understand it though. But as for me, I had heard of Firebird but not the embeddable, and of course of SQLite everywhere. Maybe it's also in part because the OSS space has limited room for this niche and SQLite kind of happened to grab the largest share.
module0000 9y ago
This may not be helpful to you, but I've always used the epoch time stored in sqlite. It's blazing fast, indexes well(integers !), and is 100% convertible to any format you might need to represent.
- hueving 9y ago
  Make sure you're using signed integers larger than 32 bits if you ever need dates before ~1970 or after ~2030.
  - duskwuff 9y ago
    SQLite's INTEGER type is 64 bits, and its REAL type is double-precision (64-bit) floating point. Either type is quite sufficient for representing a time. :)
pmontra 9y ago
That bite me more than once with Sqlite. I inherited small webapp with extremely messed up datetimes because Sqlite doesn't defend its data in any way. Same column, three different formats. Ouch. It's also a problem with the original developer but the database should not tolerate such mistakes.
- Cyberdog 9y ago
  Though I would agree that the option to have stricter type checking in SQLite would be a good thing, I don't think your criticism is fair in this case, since it sounds like a case of poor discipline on behalf of the coder(s).
- HumanDrivenDev 9y ago
  One of my (many) tasks at my current position is to maintain a SQL Server database where a lot of the date times are stored as raw strings in various different formats. If you're determined enough you can turn any datastore into a mess, though I suppose SQLite gives you less tools.

le-mark 9y ago

That's a valid observation, but the status of sqlite as a couple of ansi c file you can compile with or link to any damn thing is really what we're talking about here. And H2, awesome as it is, is no comparison.

mikeash 9y ago

I don’t know if they want to be in the business of recommending alternatives. Doing so has the unfortunate tendency of making people think it’s your problem if you use them and they don’t work out.

kragen 9y ago

For others who didn't know about H2, it's apparently an embeddable SQL database, like SQLite, but in Java: https://github.com/h2database/h2database https://github.com/h2database/h2database It's dual-licensed under the Mozilla Public License and the Eclipse Public License.

hinkley 9y ago
It grew up arm in arm with Hibernate, one of the better ORM wrappers. Fast enough for local use that you could do functional tests against your domain objects without using mocks, or crying.

coleifer 9y ago

The lack of a datetime data-type hasn't been a problem in my experience. I have, at different times, stored the timestamp as an integer representing number of microseconds since the epoch, or stored in YYYY-mm-dd HH:MM:SS (which sorts lexicographically so comparisons come out right). SQLite has some built-in facilities for working with datetimes as well, along the lines of strftime, etc. H2, being java, is in a whole different universe from SQLite...not a valid comparison in my view.

mulmen 9y ago
...in UTC. Right? RIGHT?!
- HumanDrivenDev 9y ago
  Not going to lie, after reading that I quickly went to check that "Date.now()" in javascript returned UTC.

oneweekwonder 9y ago

Not as lightweight as sqllite. But I always wonder why firebird embedded-able server[0] does not get more love? The 32-bit so library just under 7MB and then you have a full rdbms available to your application. [0] https://www.firebirdsql.org/pdfmanual/html/fbmetasecur-embedded.html https://www.firebirdsql.org/pdfmanual/html/fbmetasecur-embed...

lwf 9y ago
Well, for one, SQLite's 32-bit dll is only 858KiB :)
- oneweekwonder 9y ago
  Feature richness comparable to oracle, mysql, postgres, comes at a price. Another perspective; embeddable py 10MB, electron app 50MB[0], free pascal exe 1.5MB. So 7MB for a RDBMS seems reasonable, but not comparable to SQLite as you pointed out. [0]: https://github.com/electron/electron/issues/2003#issuecomment-135592382 https://github.com/electron/electron/issues/2003#issuecommen...
jug 9y ago
Poorly advertised? With a lack of marketing/funding I can understand it though. But as for me, I had heard of Firebird but not the embeddable, and of course of SQLite everywhere. Maybe it's also in part because the OSS space has limited room for this niche and SQLite kind of happened to grab the largest share.

module0000 9y ago

This may not be helpful to you, but I've always used the epoch time stored in sqlite. It's blazing fast, indexes well(integers !), and is 100% convertible to any format you might need to represent.

hueving 9y ago
Make sure you're using signed integers larger than 32 bits if you ever need dates before ~1970 or after ~2030.
- duskwuff 9y ago
  SQLite's INTEGER type is 64 bits, and its REAL type is double-precision (64-bit) floating point. Either type is quite sufficient for representing a time. :)

pmontra 9y ago

That bite me more than once with Sqlite. I inherited small webapp with extremely messed up datetimes because Sqlite doesn't defend its data in any way. Same column, three different formats. Ouch. It's also a problem with the original developer but the database should not tolerate such mistakes.

Cyberdog 9y ago
Though I would agree that the option to have stricter type checking in SQLite would be a good thing, I don't think your criticism is fair in this case, since it sounds like a case of poor discipline on behalf of the coder(s).
HumanDrivenDev 9y ago
One of my (many) tasks at my current position is to maintain a SQL Server database where a lot of the date times are stored as raw strings in various different formats. If you're determined enough you can turn any datastore into a mess, though I suppose SQLite gives you less tools.