CODEWITHSUNDEEP
androidsqlite
Runar Halse
Runar Halse

Reputation: 3558

Datetime aritmethics in sqlite on android

I am trying to do some datetime aritmethics in Sqllite. I have a table with two rows defined as type DATETIME. These fields are called LOG_FROM and LOG_TO. In a query on this table I try to do the following:

SELECT SUM(LOG_FROM - LOG_TO) AS TIMESPENT FROM LOG_TABLE WHERE X=Y;

This subtraction inside the SUM function does not work as intended. It will always return the number 0. I read the data via a cursor.getString.

When I store dates in the table I convert them to strings on the format yyyy-MM-dd HH:mm:ss

Upvotes: 0

Views: 35

Answers (1)

CL.
CL.

Reputation: 180060

SQLite ignores column data types. Your date strings are strings.

Your date format is supported by the built-in date functions, so you can use them to convert the dates to numbers. Assuming that you want the result to be a number of days, use julianday:

SELECT SUM(julianday(LOG_TO) - julianday(LOG_FROM)) ...

Upvotes: 1

Related Questions