Is there a way to query the creation date of a table in SQLite? I am new to SQL, overall. I just found this http://stackoverflow.com/questions/1171019/sql-server-table-creation-date-query. I am assuming that sqlite_master is the equivalent to sys.tables in SQLite. Is that correct? But then my sqlite_master table only has the columns "type", "name", "tbl_name", "rootpage" and "sql". If this is not possible in SQLite, what would be the best way to implement this functionality by myself?
A:
SQLite does not store this data itself. Like you said, the sqlite_master table doesn't have any relevant column.
There's no particularly nice way that I can come up with to implement it. You could create some sort of interface for creating tables, and have it note the time whenever you create a new one, but anything created through a different method won't go through the same process. It also looks like there's no way to set a trigger on CREATE TABLE, so that's not an option either.
Why do you want this functionality? Creating tables seems like something you wouldn't be doing very often, maybe there's a better way to approach the problem?
Chad Birch
2010-05-20 22:35:01
I'm writing a mobile application for Android and I use the database as a cache for data I load from the internet. To reduce traffic I want to fetch the data from the database instead of reloading it if it is requested repeatedly but only if the table hasn't reached a certain age since the online data might change after some time. Using a custom interface for creating tables wouldn't be a problem since my database adapter class is the only part of the program directly accessing the database. What would be a good place to store the creation dates? Should I create my own custom master table?
legr3c
2010-05-20 23:20:06
Yeah, in that case I would just create your own table similar to the `sqlite_master` one. You probably only need two columns, table name and creation date.
Chad Birch
2010-05-20 23:27:25
In case someone else needs to do something like this too: I did something different now. I integrated a time stamp in the name of the tables themselves and built a framework around that and that works fine for me now. This way I don't have another table to look after.
legr3c
2010-05-31 07:20:03