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

You can get date from db in any format(Simply as string) then convert it different date format in android.. — Chirag Ghori, Feb 11 '14 at 09:01
but can't i get in query itself?..so tht i can save my code lines — Aditi K, Feb 11 '14 at 09:02
@AditiK Read http://stackoverflow.com/questions/10832864/compare-dates-stored-as-string-in-android-sqlite-database — Pankaj Kumar, Feb 11 '14 at 09:04
@PankajKumar i am not storing my date in string format ..its in DateTime format of Sqlite — Aditi K, Feb 11 '14 at 09:07
Are you sure you get it fine if you retrieve it as a `String` ? In other word, are you sure this is only a problem with the format you're retrieving it in ? — desseim, Feb 11 '14 at 09:10
@AditiK Really? SQLite supports DateTime data type? I didn't know. — Pankaj Kumar, Feb 11 '14 at 09:13
@pankaj I am confused whether it is ryt or wrong but i got one link http://stackoverflow.com/questions/12549671/strftime-function-in-sqlite-does-not-work — Aditi K, Feb 11 '14 at 09:18
@AditiK Don't be confused.. That is for normal SQLite. Not for the SQLite which supported by Android. See laaltoo's answer. Better, go with his last paragraph :) — Pankaj Kumar, Feb 11 '14 at 09:22

score 3 · Accepted Answer · answered Feb 11 '14 at 09:15

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.

When you store raw timestamps, you will also need to store timezones in another column. Thus a common timezone aware dateformat is easier. — Christian, Feb 28 '17 at 05:20

How to format DateTime column in query for sqlite?

1 Answers1