How can I select records from a table where unixtime(integer) column contains the current date without considering the time?

Question

I'm having a table with the following fields in Android SQLite database.

In this, due_date column is INTEGER, i just want the records which matches today's date(current date) alone in due_date without considering time.

For example: If I have 3 values matching with current date, I need to return all 3 rows

Note: In my case, have to compare with current date not with current timestamp but my column type was INTEGER having unix-epoch value. My unix-epoch example is "1526565900000".

I have tried:

 date('now', 'start of day') // not working

 date('now','localtime','start of day') // not working

select strftime('%d', date('now')), name, due_date from task group by due_date;

this too not working for my requirement.

Finally as per the comments below i changed all my datetime and timestamp column into INTEGER to store as unix-epoch value before that all my column were in timestamp.

Answer 1

Step 1: First i changed the column type to int as @CL mentioned where it was declared as datetime and timestamp before.

Step 2: Then inserted all the datetime values in seconds(which is 10 digit) as @pskink said in comments.

Step 3: To get start and end value of current date in seconds i used the following java code.

//getCurrentDateTimeInMilliseconds
    public void getCurrentDateTimeInMilliseconds() {
        Date todayStart = new Date();
        Date todayEnd = new Date();

        todayStart.setHours(0);
        todayStart.setMinutes(0);
        todayStart.setSeconds(0);
        todayEnd.setHours(23);
        todayEnd.setMinutes(59);
        todayEnd.setSeconds(59);
        long endTime = todayEnd.getTime();
    }

using this you can get the start and end value of current day like this

todayStart.getTime()

it will return value in milliseconds which is 13 digit value to convert this to seconds just divide by 1000 like this,

long startDateInSeconds = todayStart.getTime/1000;
long startDateInSeconds = todayEnd.getTime/1000;

and use these values in the sqlite query based on requirement in my case the query will be as follows:

String query = "select * from task where due_date>" + startDateInSeconds + " and due_date<" + startDateInSeconds;

and then pass the query in the method of databasehelper class like this,

Cursor cursor = databasehelper.getData(query);

in DatabaseHelper.java(which should extends SQLiteOpenHelper class) file do the following code:

public Cursor getData(String query) {
        SQLiteDatabase sqLiteDatabase = this.getReadableDatabase();
        return sqLiteDatabase.rawQuery(query, null);
    }

finally nailed it with the help of @pskink, thanks for your comments helped me a lot.

Data stored like this in my db.

Answer 2

tl;dr

myPreparedStatement.setObject(                        // Generally best to use Prepared Statement, to avoid SQL Injection risk.
    1 ,                                               // Specify which placeholder `?` in your SQL statement.
    LocalDate.now( ZoneId.of( "Pacific/Auckland" ) )  // Determine today’s date as seen in the wall-clock time used by the people of a certain continent/region (a time zone).
    .atStartOfDay( ZoneId.of( "Pacific/Auckland" ) )  // Determine the first moment of the day on that date in that zone. Not always 00:00:00.
    .toInstant()                                      // Adjust that first moment of the day in that zone to the wall-clock-time of UTC.
    .getEpochSecond()                                 // Get a count of whole seconds since the epoch reference of 1970-01-01T00:00:00Z. 
)

java.time

The modern approach uses the java.time classes that supplanted the troublesome old legacy date-time classes.

In this, due_date column is INTEGER, i just want the records which matches today's date(current date) alone in due_date without considering time.

LocalDate

The LocalDate class represents a date-only value without time-of-day and without time zone.

A time zone is crucial in determining a date. For any given moment, the date varies around the globe by zone. For example, a few minutes after midnight in Paris France is a new day while still “yesterday” in Montréal Québec.

If no time zone is specified, the JVM implicitly applies its current default time zone. That default may change at any moment, so your results may vary. Better to specify your desired/expected time zone explicitly as an argument.

Specify a proper time zone name in the format of continent/region, such as America/Montreal, Africa/Casablanca, or Pacific/Auckland. Never use the 3-4 letter abbreviation such as EST or IST as they are not true time zones, not standardized, and not even unique(!).

ZoneId z = ZoneId.of( "America/Montreal" ) ;  
LocalDate today = LocalDate.now( z ) ;

If you want to use the JVM’s current default time zone, ask for it and pass as an argument. If omitted, the JVM’s current default is applied implicitly. Better to be explicit, as the default may be changed at any moment during runtime by any code in any thread of any app within the JVM.

ZoneId z = ZoneId.systemDefault() ;  // Get JVM’s current default time zone.

Epoch seconds

We need to translate that date to a number, the count of seconds since the epoch reference of first moment of 1970 in UTC.

To do that, we need to get the first moment of the day. Let java.time determine that first moment. Do not assume the day starts at 00:00:00. Anomalies such as Daylight Saving Time (DST) mean the day may start at another time such as 01:00:00. Specify a time zone to get a ZonedDateTime object.

ZonedDateTime zdt = today.atStartOfDay( z ) ;

We must adjust into UTC from our zone. Easiest way to do this is to extract an Instant. The Instant class is always in UTC by default.

Instant instant = zdt.toInstant() ;  // Adjust from our zone to UTC.

Get the number of seconds since epoch.

long secondsSinceEpoch = instant.getEpochSecond() ;  

As a shortcut, we could have asked for seconds-from-epoch directly from the ZonedDateTime class. But I wanted to make clear that we are working adjusting from a particular time zone to UTC.

long startSecondsSinceEpoch = zdt.toEpochSecond() ;  // Shortcut for adjusting from time zone to UTC, and then getting count of seconds since epoch.

We also need the ending of our time range, to find all records whose date is today’s date. We will use the first moment of the following day. This follows the usual practice of Half-Open definition of a span of time. In Half-Open, the beginning is inclusive while the ending is exclusive.

long stopSecondsSinceEpoch = ld.plusDays( 1 ).atStartOfDay( z ).toEpochSecond() ;

SQL

Now we are ready for the SQL statement. Generally best to make a habit of using a prepared statement.

String sql = "SELECT when FROM tbl WHERE when >= ? AND when < ? ; " ;
…
myPreparedStatement.setObject( 1 , startSecondsSinceEpoch ) ;
myPreparedStatement.setObject( 2 , stopSecondsSinceEpoch ) ;  

---

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

• Java SE 8, Java SE 9, Java SE 10, and later
  • Built-in.
  • Part of the standard Java API with a bundled implementation.
  • Java 9 adds some minor features and fixes.
• Java SE 6 and Java SE 7
  • Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  • Later versions of Android bundle implementations of the java.time classes.
  • For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

Answer 3

SELECT * FROM your table name where due_date BETWEEN date('now') AND strftime('%Y-%m-%d', due_date)