Is it possible to directly compare dates stored as string / text?

Question

I work in SQLite, so I store dates as soon as text.

YYYY-MM-DD hh:mm:ss

As far as I can tell, I should be able to order them or compare them with each other, since they are not worried about date functions, since any increment starts on the right and moves to the left, just like numbers, and all values ​​are numerical and non-numeric characters are always standardized, and I use 24-hour time.

I continue to see how people on the Internet mention how dates when text needs to be converted for any comparisons, but I don’t understand why they won’t work that way - as long as they are in this largest - minimum order, Obviously I can’t do the math, but Select DateTime From Table where DateTime > 2010-04-21 15:34:55 should be completely reliable, right?

Is there any exception that I don't think about?

Answer 1

The reason that you see a lot of mention of conversion when storing dates in a database is because most database engines have a "native data type for date / time." SQLite does not. It suggests that you store dates as a string or as julian floating-point dates.

In addition, some other databases have rather obscure date-to-line functions (I'm looking at you, SQL Server ), where it's hard to figure out how to get a sorted date string. The default date format for SQLites (YYYY-MM-DD HH: NN: SS) is excellently sorted and easy to read.

You will be fine with text dates when comparing or matching - if you use the correct format as you imagine.

In fact, you can even do the math using SQLite's date and time functions ; they are rather unorthodox, but surprisingly flexible.

Answer 2

This works great. SQLite does not have an actual DATE (or DATETIME) data type, as many other databases do. Your options are to save the date in a string format that will sort correctly (YYYY-MM-DD or YYYY-MM-DD hh: mm: ss) or to convert the date to a number and save it.

If you use the string format, you need to include quotation marks, of course:

 SELECT DateTime FROM Table WHERE DateTime > '2010-04-21 15:34:55' 

Answer 3

They can be compared as text, as long as the actual values ​​are what you expect from them. This is problem.

Comparing dates, like text, how it works with scissors. But SQLite makes you work with scissors all the time.

 sqlite> create table test (d1 datetime primary key); sqlite> insert into test values ('2011-31-31 08:63:00'); sqlite> select * from test; 2011-31-31 08:63:00 

. dbms, which complies with SQL standards, causes an out-of-range error when trying to insert or update values ​​such as "2011-31-31 08:63:00". But SQLite allows you to embed anything. You can even insert "Catcall" in the datetime column. (Try it.)

SQLite gives you the same "work with scissors" behavior if you use text.

 sqlite> create table test (d1 varchar(25) primary key); sqlite> insert into test values ('2011-31-31 08:63:00'); sqlite> select * from test; 2011-31-31 08:63:00 

If the values ​​do not meet your expectations, you are likely to see the following behavior.

 sqlite> create table test (d1 varchar(25), d2 varchar(25)); sqlite> insert into test values ('2011-01-01 08:00:00', '2011-01-01 08:15:00'); sqlite> select d1<d2 from test; 1 sqlite> update test set d2 = '2011+01-01 08:15:00'; sqlite> select d1<d2 from test; 0 

Answer 4

If all you do is sort and search, you should be fine. People have been using this format for many years with file names just for this purpose. It should only begin to collapse if you need to do something related to computing (for example, find all records within thirty days from a certain date).