Java :How to compare LocalDate with date storing in sqlite database?

Question

I'm trying to compare between LocalDate and date storing in sqlite database.I'm using SqliteStudio for creating my tables ,when i used SQL developer every thing is well ,but for some causes i changed my database to SQLite , i found troubles without detecting any errors,because sqlite studio save date as milliseconds. .

So how to compare 2017/09/25 with 1511906400000 ?

Code
Controller class

  DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy/MM/dd");
        LocalDate localDate = LocalDate.now();
        String date=dtf.format(localDate);
        System.out.println(date); //print 2017/09/24
        /*now i will compare this date to saved date in database*/
        Paiments = new EMPDao().FindPaiment(dtf.format(localDate));

DAO class

public ObservableList<Employer> FindPaiment(String format) {

    Employer employer;

    ObservableList<Employer> employers = FXCollections.observableArrayList();

    String querySelectEmployer = "SElECT * FROM EMPLOYER WHERE DPayment =" + "'" + format + "'" + " and STAT is null";

    try {

        ResultSet resultSet = Dbaconnection.getConnection().createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY).executeQuery(querySelectEmployer);

        while (resultSet.next()) {

            employer = new Employer(resultSet.getString("NFILE"), resultSet.getString("FNAME"), resultSet.getString("LNAME"), resultSet.getDate("BDATE"), resultSet.getString("PDATE"), resultSet.getString("NPHONE"), resultSet.getString("CCP"), resultSet.getDate("EDATE"), resultSet.getDate("SDATE"), resultSet.getString("POSTE"), resultSet.getString("CATEGORY"), resultSet.getString("NUMEROSS"), resultSet.getString("PAYMENT"), resultSet.getString("ETAT"), resultSet.getDate("DPAYMENT"), null);
            employers.add(employer);
        }
        resultSet.close();

    } catch (SQLException ex) {
        ex.printStackTrace();
    }

    return employers;

}

localDate has this format:2017/09/25

Dpayment in database has this format :1506290400000

Suggestion: Use PreparedStatement. I can't think of a good reason to ever concatenate into a SQL query — OneCricketeer, Sep 24 '17 at 17:40
By the way, Sqlite doesn't have a "Date type". https://sqlite.org/datatype3.html#date_and_time_datatype — OneCricketeer, Sep 24 '17 at 17:49

OneCricketeer · Accepted Answer · 2017-09-24T18:04:17.087

3

Not sure what you mean that you have Date types in Sqlite. You store as TEXT, REAL, or INT. And none of those support milliseconds.

You have a few options.

1) invert your logic to reformat your DPayment to your Java format

Forget the Java code for a moment, and connect to the database externally. Play around with this query. This also does conversion from milliseconds to seconds

SELECT strftime('%Y/%m/%d', DPayment / 1000) FROM EMPLOYER

2) Change your database to use the supported date format of YYYY-MM-DD, and update Java code accordingly

3) Make your Java code use (milli)seconds in the query rather than a formated date string

edited Sep 24 '17 at 18:04

answered Sep 24 '17 at 17:59

OneCricketeer

179,855
19
132
245

Yeah ,i'm using sqlite studio and i have type `Date` and `DateTime`. – Menai Ala Eddine - Aladdin Sep 24 '17 at 22:58
@MenaiAlaEddine Again, as others have noted: No you do *not* have `Date` and `DateTime` columns in a SQLite database. Quoting [the doc](https://www.sqlite.org/datatype3.html): *SQLite does not have a storage class set aside for storing dates and/or times.* [SQLite is meant to be an alternative to storing text in files, not meant to be a competitor to serious databases.](http://use-the-index-luke.com/blog/2014-05/what-i-learned-about-sqlite-at-a-postgresql-conference) For more serious needs use another solution such as [Postgres](https://en.wikipedia.org/wiki/PostgreSQL). – Basil Bourque Sep 24 '17 at 23:46
1

@BasilBourque ,You are right ,when the GUI of [https://sqlitestudio.pl] display `Date` and `Date time` , i thought it is type of `date` but in reality it is `int`.Thank you very much. – Menai Ala Eddine - Aladdin Dec 12 '17 at 00:12

score 0 · Answer 2 · answered Sep 24 '17 at 17:34

You can have SQLite compute the current date:

String querySelectEmployer = "SELECT * FROM EMPLOYER WHERE DPayment = date('now') and STAT is null";

This gets around issues with concatenating strings, and is easier to read, but comes with some caveats: you cannot pass any date to FindPaiment, and it does not compare your client's date with the server's date. If these caveats do not work for you, you can use one of SQLite's date functions (https://sqlite.org/lang_datefunc.html).

@cricket_007 ,but in sqlite i have not this format of YYYY-MM-DD because it save dates as millisecondes. — Menai Ala Eddine - Aladdin, Sep 24 '17 at 17:43

Menai Ala Eddine - Aladdin · Answer 3 · 2017-09-25T10:09:17.137

When i created my table i used sqlite studio 3.3.1 like this:

And this the DDL

CREATE TABLE EMPLOYER (
    NFILE    VARCHAR2 (20) PRIMARY KEY,
    FNAME    VARCHAR2 (20),
    LNAME    VARCHAR2 (20),
    BDATE    DATE,
    PDATE    VARCHAR2 (20),
    NPHONE   VARCHAR2 (20),
    CCP      VARCHAR2 (20),
    EDATE    DATE,
    POSTE    VARCHAR2 (20),
    CATEGORY VARCHAR2 (20),
    PAYMENT  VARCHAR2 (20),
    DPAYMENT DATE,
    ETAT     VARCHAR2 (20),
    NUMEROSS VARCHAR2 (20),
    SDATE    DATE,
    IMAGE    BLOB,
    STAT     VARCHAR2 (20) 
);

Because sqlite 3.3.1 has not a format of "YYYY-MM-DD" for Date type and it stores a data as long for example 2017-09-26 = 1506376800000 on sqlite .So according to Convert LocalDate to LocalDateTime or java.sql.Timestamp it should convert localdate to date and get time (long format) from date and make the comparison.

        LocalDate localDate = LocalDate.now();
        long currentDate=String.valueOf(java.sql.Date.valueOf(localDate).getTime());
        //Pass the paramater as long (12366332) not as date(2017/09/26)
        Paiments = new EMPDao().FindPaiment(currentDate);

Sqlite does have that format. You just need to excessively use `strftime()` to use that format — OneCricketeer, Sep 25 '17 at 01:30
I really do not think you're actually using sqlite. You can clearly read the documentation and see DATE and VARCHAR2 are not existing types. — OneCricketeer, Sep 25 '17 at 12:38
In other words, sure. The GUI gives you the option, but it's actually a REAL/TEXT/INT value internally in the database — OneCricketeer, Sep 25 '17 at 12:49

Java :How to compare LocalDate with date storing in sqlite database?

3 Answers3