Objective
My objective is to implement a simple sqlite viewer in which it queries the user selected db table and show records in a SWT table in java. If there are any date columns they should appear in understandable format.
Question
To achieve above objective the program should dynamically understand what are the date columns. There is no data type date in sqlite. Date is stored as integers.
How to identify whether that particular column is supposed to be a date?
Thank you!
Unfortunately It's a bit more complicated than that (
Dates can be stored in a number of formats as per the sqlite spec
See section 2
further an application can choose to store a date in any other format it chooses.
Chrome dates, Unix 10 & 13 digit dates, IOS NSdates and Micorsoft 64 bit file times are all common. But If I chose to save a date as a float recording the number of half seconds since my birthday then there is absolutely nothing stopping me doing so.
That said - I think it not unreasonable to start with the defaults above, and use the functions at the page below along with anything you have in your java toolkit to convert them.
https://
As to dynamically understanding what a date column is assuming an SWT table doesn't have a fomat that prescribes this. You could try the column name to see if that gives a clue (could easily fail) or you could iterate though all the columns in your table convert every row and see if the converted date made sense - note not just the conversion but given the type of data is it sensible (if the table stores your class mates date of births and you get a date that decodes correctly to some time in 1901 then it is not sensible).
BTW this is posted without having a clue what a swt table is )
Oh - and just because a SQLite table is defined as an integer - it doesn't mean you can't store a float, or even text, in it - see type affinity in that first page above.
Finally there is a load of information about different time formats in the article "A Brief History of of Timestamps" on my web site here - http//
SQLite is hardly standardized, so it would depend on the type of database you have.
My scenario I have pulled some data from Android phone. So as a college work I am programming a piece of code in which it will query SQLite db tables and show the records in java GUI app. As an example if sms/mms it will take mmssms.db file and show the content.
In order to format data I have to know in advance if there is any dates stored columns.
(Hope now it is more clear)
As to dynamically understanding what a date column is assuming an SWT table doesn't have a fomat that prescribes this. You could try the column name to see if that gives a clue (could easily fail) or you could iterate though all the columns in your table convert every row and see if the converted date made sense - note not just the conversion but given the type of data is it sensible (if the table stores your class mates date of births and you get a date that decodes correctly to some time in 1901 then it is not sensible)
SWT is java GUI component. Forget SWT ). I want to mean that my java program running on desktop has no idea about the SQLite db table columns and their type. That is why I want to dynamically to identify is that particular column is related to a date.
I already tried on first suggestion based on column name. But it was not success all the time. Then I have to define set of label names.
I will give a try on second method.
Mean time please comment on this if you see other alternative based on my scenario.
Many thanks for all of your kind helps and suggestions