Why doesn't SQLiteStudio (and others) display the date and time in the default readable format?
Today I had to use a SQLite database for the first time and I was really wondering about column mapping DATETIME
for example 1411111200
. Of course, internally it has to be stored as some integer value in order to be able to do math with it. But who wants to see this in the mesh outlet that is obvious to the human eye?
I've even tried two programs, SQLiteStudio and SQLite Manager, and both don't even have the option to change this (at least I couldn't find it).
Of course, with my knowledge of SQL, it didn't take long to find out what the values mean - this query displays it as I expected:
select datetime(timestamp, 'unixepoch', 'localtime'), * from MyTable
But this is very inconvenient when working with the GUI Tool. So why? Just because? Unix nerds? Or was I just being wrong because I accidentally tried only 2 tools which are bad?
(I also appreciate comments as to which tools to use or where I can find hidden settings.)
source to share
The question implies that the data type column DATETIME
can only contain valid dates. But this is not the case in SQLite: you can put any number or string value and it will be stored and displayed as it is.
To see what might be the most "natural" way for a timestamp in SQLite, I created a table like this:
CREATE TABLE test ( timestamp DATETIME DEFAULT ( CURRENT_TIMESTAMP ) );
As a result, the display is displayed in readable format ( 2014-09-22 10:56:07
)! But it actually gets saved as a string and I can't imagine any serious software developer wanting to do that. Any comments?
This is the original database from the question having datetimes as unixepoch, not because of its table definition, but because the data inserted was like that. This was probably the best way to do it.
So, the answer is that these tools cannot display the date and time in a humanoid format because they cannot know how it was encoded. It could be the number of seconds since 1970, or something else, and it may even differ from line to line. What a mess.
From Wikipedia :
A common criticism is that the SQLite type system lacks the data integrity mechanism provided by statically typed columns in other products. [...] However, it can be implemented with restrictions eg CHECK (typeof (x) = 'integer').
From the authors :
[...] most of the other SQL mechanisms are statically typed and therefore some people think that using the manifest is a bug in SQLite. But the authors of SQLite strongly believe that this is a feature. Using the character set in SQLite is a smart design decision that has proven in practice that SQLite is more reliable and easier to use, especially when used in conjunction with dynamically typed programming languages such as Tcl and Python.
source to share
Probably because sqlite doesn't have a first class date type - how did the GUI tool know which columns should contain dates?
source to share