CODEWITHSUNDEEP
databasesqlitetimestampstrftime
bambuste
bambuste

Reputation: 147

SQLite: Extract time from string using strftime

I have database column with time (string is used as data type of the column) in this format '30.09.2014 09:03:01'. I'd like to get timestamp from that column using:

SELECT id, strftime('%d.%m.%Y %H:%M:%S', date) AS time FROM table;

But I get empty column with time. I think there is a problem with dots as a separators. Can anyone help me with that?

Upvotes: 0

Views: 1025

Answers (1)

user650881
user650881

Reputation: 2505

The strftime format string is used to specify the output format for printing a date that is supplied in one of the standard formats. The input formats accepted are one of the following:

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD

You may need to do some string manipulation to reformat the date strings into a format that sqlite can interpret. Often it is convenient to reformat the date when inserting the data initially.

Reference sqlite documentation.

Upvotes: 1

Related Questions