3

Today I had to use a SQLite database for the first time and I really wondered about the display of a DATETIME column like 1411111200. Of course, internally it has to be stored as some integer value to be able to do math with it. But who wants to see that in a grid output, which is clearly for human eyes?

I even tried two programs, SQLiteStudio and SQLite Manager, and both don't even have an option to change this (at least I couldn't find it).

Of course with my knowledge about SQL it didn't take long to find out what the values mean - this query displays it like I expected:

select datetime(timestamp, 'unixepoch', 'localtime'), * from MyTable

But that's very uncomfortable when working with a GUI Tool. So why? Just because? Unix nerds? Or did I just get a wrong impression because I accidentally tried the only 2 Tools which are bad?

(I also appreciate comments on which tools to use or where I can find the hidden settings.)

maf-soft
  • 2,335
  • 3
  • 26
  • 49
  • I can read `1411111200`. Does that mean I'm not a human? – Damien_The_Unbeliever Sep 19 '14 at 14:46
  • But what the average human wants to know when reading the timestamp column, is this: 2014-09-19 09:20:00 – maf-soft Sep 19 '14 at 15:22
  • Sorry, it was a silly comment based on your (implied) description of `1411111200` as not being "human-readable" based on your title. But the more serious point (not yet made) is that many systems understand unix-epoch based datetime values. So keeping them in that form involves fewer translations - would you rather a system performs a number of complex formatting translations and it not be obvious that what's being shown to you is completely different from what's actually been stored? – Damien_The_Unbeliever Sep 19 '14 at 17:27
  • I don't care how it is stored and agree that keeping it that way makes sense. I am just curious if the two programs have a good reason not decoding it for display. – maf-soft Sep 19 '14 at 17:42

2 Answers2

2

Probably because sqlite doesn't have a first-class date type — how would a GUI tool know which columns are supposed to contain dates?

Eevee
  • 47,412
  • 11
  • 95
  • 127
  • Thanks for the link, it's helpful and interesting, but the GUI can know it's a datetime because the table definition says that. When editing the table structure I can select from many data types including `DATE` and `DATETIME`. – maf-soft Sep 19 '14 at 15:19
  • Ok, reading your link again thoroughly, leads to a plausible answer to my question: the column datatype `DATETIME` doesn't mean it can only hold valid datetimes. You can put any number or string value and it will be stored and displayed like it is. I don't like that. – maf-soft Sep 22 '14 at 10:25
  • Your answer helped me to find something, but it is, in my opinion, not an acceptable answer itself... So I am waiting for a better answer or improvement of this one. – maf-soft Sep 22 '14 at 10:29
1

The question implies that a column of datatype DATETIME can only hold valid datetimes. But that's not true in SQLite: you can put any number or string value and it will be stored and displayed like it is.

To find out what the most "natural" way for a timestamp in SQLite would be, I created a table like this:

CREATE TABLE test ( timestamp DATETIME DEFAULT ( CURRENT_TIMESTAMP ) );

The result is a display in human readable format (2014-09-22 10:56:07)! But in fact it is saved as string, and I cannot imagine any serious software developer who would like that. Any comments?

That original database from the question, having datetimes as unixepoch, is not because of its table definition, but because the inserted data was like that. And that was probably the best possible option how to do it.

So, the answer is, those tools cannot display the datetime in human readable format, because they cannot know how it was encoded. It can be the number of seconds since 1970 or anything else, and it could even be different from row to row. What a mess.

From Wikipedia:

A common criticism is that SQLite's type system lacks the data integrity mechanism provided by statically typed columns in other products. [...] However, it can be implemented with constraints like CHECK(typeof(x)='integer').

From the authors:

[...] most other SQL database engines are statically typed and so some people feel that the use of manifest typing is a bug in SQLite. But the authors of SQLite feel very strongly that this is a feature. The use of manifest typing in SQLite is a deliberate design decision which has proven in practice to make SQLite more reliable and easier to use, especially when used in combination with dynamically typed programming languages such as Tcl and Python.

maf-soft
  • 2,335
  • 3
  • 26
  • 49
  • "*I cannot imagine any serious software developer who would like that*" - I agree. "*Any comments*" - use a different DBMS? –  Sep 22 '14 at 11:24