SQLite Forum

Best practice: Save partial dates

Best practice: Save partial dates

(1) By Tobias (TBauer) on 2021-06-29 04:14:09 [link] [source]


I have to save partial dates into a SQLite database. What is the best practice for that? In my case, I have no possibility to store the three values seperate.

  • complete date: 2021-06-29
  • year-month: 2021-06
  • year: 2021
  • only month: 06
  • only day: 29

My oppoinion is, to use a TEXT-field and seperate with a dash (like iso-8601).

  • complete date: 2021-06-29
  • year-month: 2021-06-
  • year: 2021--
  • only month: -06-
  • only day: --29

So I have to opportunity to split this string in my program and work with this values in my code.

Another important thing is, that I cannot use an integer. I have dates before 1970 and so I can use TEXT and REAL. So I think the TEXT ist the best option.

Does someone have a better idea? Thank you!

(2) By Larry Brasfield (larrybr) on 2021-06-29 17:27:27 in reply to 1 [link] [source]

I have to save partial dates into a SQLite database ... I have no possibility to store the three values seperate.

Taking those as givens, the ISO-8601 representation is a good choice. And for text representations of dates, it is well respected and may be considered "best practice" by some.

Since you have provided no facts behind your givens, nor any inkling as to what kinds of processing will be done at what relative frequencies, I see no basis upon which to suggest a better idea or even adjudge one as better or worse.

(3) By Ryan Smith (cuz) on 2021-06-29 17:46:40 in reply to 1 [link] [source]

Saving partial dates is not a "practice" by any stretch of the meaning of the word, even though you may not be the first ever to need to do it.

If it isn't a "practice", then there can be no "best practice".

You may as well ask "What's considered as the 'correct' way to eat ice-cream vinaigrette?". Hopefully nobody knows.

What you need to consider is the use case, and any potential future calculation you might be doing on it.

If 'twas me...

I would put it in a string of exact length with placeholders, perhaps periods for legibility's sake, so that it makes the ISO8601 form (for example): yyyy-mm-dd with placeholder like Asterisk, space, period, or such. Preferably things that will not be trimmed (like spaces) and that will not be confused with real date-time separators, like "-" and ":".

So that the following date representations become:

    complete date:    2021-06-29
    year-month:       2021-06-..
    year:             2021-..-..
    only month:       ....-06-..  
    only day:         ....-..-29


This way you can easily make a function or regex to ensure it is of the correct length, correct form, you know where every part of it is by character index and in general can see ata glance while looking over a list of them, which are complete and which miss what parts.

Downside: It'll take around 35% more space on average given a random distribution of incomplete dates, but it may matter much more or much less for your use case, depending on whether you "mostly" have complete dates, or "mostly" incomplete dates, if space is a consideration.

(5) By Scott Robison (casaderobison) on 2021-06-29 17:51:05 in reply to 3 [source]

You beat me by two minutes. And expressed it much better that I did.

I mean, "+1"

(6) By Ryan Smith (cuz) on 2021-06-29 18:03:24 in reply to 5 [link] [source]

You know what they say: Great minds think alike... and fools never differ.

Sighs of relief that you did not propose the same placeholder character :)

(7) By Tobias (TBauer) on 2021-06-29 19:09:36 in reply to 3 [link] [source]

That's a great idea and yes regexp are another great idea!

The space ist not the problem.

Thank you for your replies!

(8) By Roman (moskvich412) on 2021-06-29 19:35:19 in reply to 3 [link] [source]

If the task is to use one TEXT column to save as much information as available (only year of birth, but not the day, or day but not the year), then any placeholder will work but might affect ordering when sorting:

2021-06-29 versus XXXX-06-XX, which of them you want to be before which?

If you have partial or full information and intend to search separately by year, or month or day (select all people born on a 29th), then create three separate columns for year, month and day and indexes for each. Some will have NULLs.

Enforcing date format using CHECK() will be more involved with the placeholders. Without them:

CREATE TABLE a(visitDate TEXT CHECK( date(visitDate) = visitDate OR visitDate IS NULL))

(9) By Ryan Smith (cuz) on 2021-06-29 21:14:21 in reply to 8 [link] [source]

This is a great added comment, sorting is almost always important, and moreso with dates, so it is good to consider.

Another thought - avoid placeholder characters that can be used for string manipulation or insertion, like ? and : (which carry meaning in SQL parameters), or {, }, or / which are frequently used in Pearl/PHP strings, file paths, etc.

None of these should be a real problem when following good quoting or escaping practices, but for me, the less need to escape/quote anything, the better.

(4.1) By Scott Robison (casaderobison) on 2021-06-29 17:49:12 edited from 4.0 in reply to 1 [link] [source]


(10) By tom (younique) on 2021-08-08 19:47:04 in reply to 1 [link] [source]

I'm aware this question is some days old meanwhile, but if I were you, I'd use TEXT and store the date information as JSON:

INSERT INTO table(date) VALUES (json_object('year',2021,'month',6,'day',NULL));

(11) By Ryan Smith (cuz) on 2021-08-08 20:32:12 in reply to 10 [link] [source]

While there might be some sense to this suggestion in terms of data handling-ability (and perhaps prettiness?), it is cumbersome, requires intermediate encoding/decoding and will be orders of magnitude slower. JSON (and other object orientated storage types) are typically more sensible precisely where form and format varies, and in this case the format is very much set in stone by contrast.