How to format DateTime column in query for sqlite?

Question

I want my datetime table column in %Y-%m-%d %H:%M:%S format

I am using

SELECT AIRPORT_NAME , strftime('%Y-%m-%d %H:%M:%S', 'CREATETS')as crts FROM AIRPORT_MASTER where AIRPORT_MASTER_ID = 1;

CREATETS is my datetime column having data in "21-03-2011 12:00:00.000000" format

This as query but doesn't work fine in my case, sqlite don't show any error but no output in this case help me to get

"2011-03-21 08:55:36" as output format

Answer 1

First, 'CREATETS' is a string literal and not a column name. If you need to refer to a column, remove the '' quotes.

Second, 21-03-2011 12:00:00.000000 is not a time string as understood by sqlite and attempting to convert it using datetime functions will result in null.

Technically it is possible to convert the your datetime values to a time string understood by sqlite using SUBSTR() to extract parts of the value and reorder the fields.

However, SQL is not the right place to put your presentation/formatting code in. Do that in your Java code instead. Also, it would be easier if you just stored timestamps in a "raw" format such as unix timestamp (seconds since epoch) or Java milliseconds timestamp instead of formatted strings.