Couldnt see anything when i needed this and i worked it out so thought id share for anyone thats needing to do the same
I have 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 <i>select * from table</i> i get a string of seconds to 4 decimal places.
Ended up working out that you need to do a full select with column names and then use <i>strftime(column) as columnname</i> in the select statement.
For Example <i>select field1, field2, field3, strftime(field4) from table</i> 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
for example <i>select field1, field2, field3, strftime(’%Y-%m-%d %H:%M:%S’, field4) from table</i> 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 <i>CREATE TABLE IF NOT EXISTS tablename (id INTEGER PRIMARY KEY AUTOINCREMENT, field1, field2, field3 DATETIME, field4, field5 INTERGER);</i> then store data when creating as datetime.