SQLITE datetime woes
Came across this issue recently in some mobile dev i was doing using Corona SDK where i was trying to get formatted datetime from entries in a database (something i have done a million times) however getting datetime from sqlite is not as easy as regular SQL or NOSQL.
Coming from a strong database background datetime has always been the most trusted way of proving time positioning of elements to a user. Sqlite, which is the basis of mobile app database development, seems to be an exception to how it handles the formatting of the output.
I had a database with user input one of which is datetime of when an event is to occur. Im storing it as a datetime field in the database.
When i did an output on the field using “select * from table” i get a string of seconds to 4 decimal places.
To get formatted datetime from sqlite you need to do a full select with column names and then use “strftime(column) as columnname” in the select statement.
Example: “select field1, field2, field3, strftime(field4) from table” which will return the default YYYY-MM-DD HH:MM:SS format.
For formatting you can do the same but using the following before the output field
- %d day of month: 00
- %f fractional seconds: SS.SSS
- %H hour: 00-24
- %j day of year: 001-366
- %J Julian day number
- %m month: 01-12
- %M minute: 00-59
- %s seconds since 1970-01-01
- %S seconds: 00-59
- %w day of week 0-6 with Sunday==0
- %W week of year: 00-53
- %Y year: 0000-9999
Example: “select field1, field2, field3, strftime(‘%Y-%m-%d %H:%M:%S’, field4) from table” will return the date from the database as YYYY-MM-DD HH:MM:SS
Remember for this to work you need to have a column set as datatime in the database when its created for example “CREATE TABLE IF NOT EXISTS tablename (id INTEGER PRIMARY KEY AUTOINCREMENT, field1, field2, field3 DATETIME, field4, field5 INTERGER);” then store data when creating as datetime.