CODEWITHSUNDEEP
sqlitedatetime
barbara_ko
barbara_ko

Reputation: 63

Compare java Date to SQLite Datetime

I want to compare two dates, the today's date and the date when my data has been added to the database. I can convert my Date to String and String to Date, but in my database the type of the "date" column is DATETIME. (I am using SQLite) How could I compare the two dates?

My goal is to add only 1 row / day into the database

I atteched some example code:

if(currentDate.equals(dateAdded))
//some warning...
else
applyNewDataToDB();

Thank you, any answer would be appreciated

Upvotes: 0

Views: 154

Answers (1)

B Aerts
B Aerts

Reputation: 134

In SQLite, all native timestamps are date/time values - but using the formatting functions, you can filter out only time, or only dates, or both: see https://www.sqlite.org/datatype3.html combined with https://www.sqlite.org/lang_datefunc.html

With the following table layout : 

CREATE TABLE myTable (ID INTEGER primary key, theData VARCHAR(4), theDatetime REAL) ;

the query :

SELECT ID, theData 
FROM myTable 
WHERE strftime('%Y-%m-%d', theDatetime,'unixepoch') >="2019-11-04" ;

is equivalent to :

SELECT ID, theData 
FROM myTable 
WHERE theDatetime >= 1572853740.0 ;

Upvotes: 1

Related Questions