java sqlite 日期比较,TimeStamp Java和SQLite之间的区别

Hello I have and SLQLite database in which I have table water_logs

CREATE TABLE water_logs(

_id INTEGER PRIMARY KEY AUTOINCREMENT,

amount REAL NOT NULL,

icon INTEGER NOT NULL,

date INTEGER NOT NULL);

I store date in milliseconds.

Calendar cal = Calendar.getInstance();

cal.getTimeInMillis();

My problem is I want to get the day from the my date column using strftime function. The problem is tjat java calendar timestamp is different from SLQLite time stamp

1436859563832 --> result from cal.getTimeInMillis();

1436607407--> SELECT strftime('%s','now')

What I'm actually trying to do is to group records by day. The following SQL query works just fine if value of SELECT strftime('%s','now') is paste in the date column

SELECT SUM(amount), date(`date`) FROM water_logs

GROUP BY date(`date`, 'unixepoch')

解决方案

Seems to me that you are using 2 different value types.

When you use

Calendar cal = Calendar.getInstance();

long time = cal.getTimeInMillis();

The output value is in Milliseconds, as described here.

While when you use

strftime('%s','now')

The output value is in Seconds, as described here.

So, that might be the cause for the mismatch between the two values.

Of course that the value in seconds might undergo some rounding which might change its value a little.