SQLITE datetime woes

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.