how to query today in sqlite

问题:

I have a local database in android using sqlite , i used active android and my table pojo is like this:

@Column(index = true, unique = true , onUniqueConflict = Column.ConflictAction.REPLACE)
public long taskId;
@Column( index = true)
public String taskIdno;
@Column()
public String taskDescription;
@Column()
public String taskTypeId;
@Column()
public String customerName;
@Column()
public String customerContact;
@Column()
public String address;
@Column(index = true)
public int status;
@Column()
public Date startSchedule;
@Column()
public int first_visit;
@Column()
public String coordinates;
@Column()
public int radius;
@Column()
public long location_type_id;
@Column()
public long duration;
@Column()
public long taskType_id;

My problem is that my query for today doesnt work, Or there is something im missing here.

  public static List<AATask> allTaskOrderedByDate(String asc_desc){
        From from = new Select().from(AATask.class).where("strftime('%Y-%m-%d', startSchedule ) = strftime('%Y-%m-%d' , DATE('now'))").orderBy("startSchedule ".concat(asc_desc));
        Log.w(TAG , from.toSql());
        return from.execute();
    }

startSchedule is converted to timeInMilliseconds(correct me if im wrong) default to active android Date object, How do I get today query?

回答1:

Your query should be like this :

select * from <table> where <date_col> = current_date

SQLite allows selecting current date, time and timestamp like this :

select current_date, current_timestamp, current_time from <table>

The output of above query will be like this :

"2015-11-04","2015-11-04 04:08:47","04:08:47"

If you are storing date as integer or unix epoch then do the following : 1. Get the current time in milliseconds in JAVA. Lets say it is current_time_in_mill

Then use the following query

select date(current_time_in_mill, 'unixepoch') from <table>;

Example try this : select date(1092941466, 'unixepoch')

回答2:

If you are storing your date as INTEGER, try this:

select * from my_table where date(dateColumn) = date('now');