Cannot get data from SQLite Room Database using dates

Question

I have a typeconverter that goes like this

public class DateConverter {
    @TypeConverter
    public static Date convertLongToDate(Long dateLong) {
        return dateLong == null ? null : new Date(dateLong);
    }

    @TypeConverter
    public static Long convertDateToLong(Date date) {
        return date == null ? null : date.getTime();
    }
    
    ...
}

And I use it in my database

@Database(entities = { LoginEntity.class,
        Transaction.class,
        UnsentTagNumbers.class }, version = 11, exportSchema = false)
@TypeConverters(DateConverter.class)
public abstract class ApplicationDatabase extends RoomDatabase {
    public abstract LoginDao loginDao();
    ...
}

In inserting the transaction to the database, I do it like so.

public void recordTransaction() {
    transaction.setTransactionDate(Calendar.getInstance().getTime()); <==== Please take note on the way that I use the current date.
    transaction.setTransactionType(getApplication().getString(R.string.exit_scan));
    ...
    transactionRepository.insert(transaction);
}

In my DAO

@Query("SELECT * FROM TRANSACTION_TABLE WHERE transactionDate >= :fromDate AND transactionDate <= :toDate AND staffId = :staffId")
LiveData<List<Transaction>> getTransactionSummaryByDates(Date fromDate, Date toDate, String staffId);

This is how I search for the transactions by dates

public void onClick(View v) {
    switch (v.getId()) {
    case R.id.btnStartDate:
            if(!hasSelectedStaffId) {
                Toast.makeText(this, "Select a staff id first.", Toast.LENGTH_SHORT).show();
                return;
            }
            Calendar cldr = Calendar.getInstance();
            int day = cldr.get(Calendar.DAY_OF_MONTH);
            int month = cldr.get(Calendar.MONTH);
            int year = cldr.get(Calendar.YEAR);
            dpdStartDate = new DatePickerDialog(this,
                    new DatePickerDialog.OnDateSetListener() {
                        @Override
                        public void onDateSet(DatePicker view, int year, int monthOfYear, int dayOfMonth) {
                            hasSelectedStartDate = true;
                            startDate = new Date(year, monthOfYear, dayOfMonth, 0, 0, 0);
                            btnStartDate.setText(dayOfMonth + "/" + (monthOfYear + 1) + "/" + year);
                        }
                    }, year, month, day);
            dpdStartDate.show();
            break;
        case R.id.btnEndDate:
            cldr = Calendar.getInstance();
            day = cldr.get(Calendar.DAY_OF_MONTH);
            month = cldr.get(Calendar.MONTH);
            year = cldr.get(Calendar.YEAR);
            dpdEndDate = new DatePickerDialog(this,
                    new DatePickerDialog.OnDateSetListener() {
                        @Override
                        public void onDateSet(DatePicker view, int year, int monthOfYear, int dayOfMonth) {
                            endDate = new Date(year, monthOfYear, dayOfMonth, 23, 59, 59);
                            btnEndDate.setText(dayOfMonth + "/" + (monthOfYear + 1) + "/" + year);
                            if (hasSelectedStartDate && hasSelectedStaffId) {
                                viewModel.getTransactions(startDate, endDate, selectedStaffId);
                                transactionAdapter.setTransactionList(transactionList);
                                transactionAdapter.notifyDataSetChanged();
                            }
                        }
                    }, year, month, day);
            dpdEndDate.show();
            break;
    }
}

And this is in my viewmodel to get the transactions

public void getTransactions(Date startDate, Date endDate, String staffId) {
    transactionList = transactionRepository.getTransactionSummaryByDates(startDate, endDate, staffId);
}

How come, I do not get any information from the database? If I check my database, the transactions are recorded. Please see image.

This is the parameters I send so I can get via dates

startDate: Wed Jan 25 00:00:00 GMT+08:00 3922 endDate: Thu Jan 26 23:59:59 GMT+08:00 3922 staffId: 10164

But if I convert my dates that I get from the datepicker to epoch like this:

if (hasSelectedStartDate && hasSelectedStaffId) {
    Long lStartDate = startDate.getTime();
    Long lEndDate = endDate.getTime();
                                  
    viewModel.getTransactions(lStartDate, lEndDate, selectedStaffId);
                                 
    //viewModel.getTransactions(startDate, endDate, selectedStaffId);
    //setupTransactionList();
                            }

I've noticed that in my database, the date is 16431... But when I query and I convert my date parameters, it is 6060....

How do I make this right?

In your database, the date is stored as epochs (in milliseconds). — kiner_shah, Jan 26 '22 at 06:31
Does this answer your question? [Convert UTC Epoch to local date](https://stackoverflow.com/questions/4631928/convert-utc-epoch-to-local-date) — kiner_shah, Jan 26 '22 at 06:33
@kiner_shah So do I have to change the way I insert the dates or the way I get the dates? — Ibanez1408, Jan 26 '22 at 06:33
You probably have to change the way you convert the dates from epochs after retrieving from the database. — kiner_shah, Jan 26 '22 at 06:35
So I need to convert the dates I get from the datepicker and convert it to epochs and use it to retrive data from the database? — Ibanez1408, Jan 26 '22 at 06:36
But if I convert the dates in the date picker and convert it to epochs, then I would have to change my parameters from (Date, Date, String) to (Long, Long, String). Is that what you mean? — Ibanez1408, Jan 26 '22 at 06:39
You can accept input as Date and convert to Long inside the function. Or change the signature - whatever is convenient to you. — kiner_shah, Jan 26 '22 at 06:40
I did this the first time and I did it again, but the thing is; the times in the database starts with 16... while the times in the data pickers starts with 61... — Ibanez1408, Jan 26 '22 at 06:47
Please don't post code as image. Copy and paste the code in the post itself. Also, make sure it's a [mre]. I cannot see where this function is called from. — kiner_shah, Jan 26 '22 at 06:52
I actually had the image and code. The code was a little above the second image. I just showed the image as a reference to the values that I was getting. But I edited the question to post the a clearer code. — Ibanez1408, Jan 26 '22 at 06:59
But where is that part of code located - in which function/class? What is startDate and what is filled in it? — kiner_shah, Jan 26 '22 at 07:04
The startDate is on this line of the onCLick => startDate = new Date(year, monthOfYear, dayOfMonth, 0, 0, 0); and the endDate is on this line of the onClick => endDate = new Date(year, monthOfYear, dayOfMonth, 23, 59, 59); — Ibanez1408, Jan 26 '22 at 07:06
Not sure what are the values of year, month and day, but should be as per https://docs.oracle.com/javase/8/docs/api/java/util/Date.html. Year should be yyyy-1900. That is if year is 2010, `year` should hold `2010 - 1900 = 110`. — kiner_shah, Jan 26 '22 at 07:10

score 0 · Answer 1 · answered Jan 26 '22 at 07:31

The mistake I had was in the codes of my onClick(View ...). I just converted my Date Pickers to Calendar and got the time from it like so..

public void onClick(View v) {
    switch (v.getId()) {
        case R.id.btnStartDate:
            if(!hasSelectedStaffId) {
                Toast.makeText(this, "Select a staff id first.", Toast.LENGTH_SHORT).show();
                return;
            }
            Calendar cldr = Calendar.getInstance();
            int day = cldr.get(Calendar.DAY_OF_MONTH);
            int month = cldr.get(Calendar.MONTH);
            int year = cldr.get(Calendar.YEAR);
            dpdStartDate = new DatePickerDialog(this,
                    new DatePickerDialog.OnDateSetListener() {
                        @Override
                        public void onDateSet(DatePicker view, int year, int monthOfYear, int dayOfMonth) {
                            hasSelectedStartDate = true;
                            cldr.set(year, monthOfYear, dayOfMonth, 23, 59, 59);
                            startDate =  cldr.getTime(); //new Date(year, monthOfYear, dayOfMonth, 0, 0, 0);
                            btnStartDate.setText(dayOfMonth + "/" + (monthOfYear + 1) + "/" + year);
                        }
                    }, year, month, day);
            dpdStartDate.show();
            break;
        case R.id.btnEndDate:
            if(!hasSelectedStaffId) {
                Toast.makeText(this, "Select a staff id first.", Toast.LENGTH_LONG).show();
                return;
            }
            if(!hasSelectedStartDate) {
                Toast.makeText(this, "Select a start date first.", Toast.LENGTH_LONG).show();
                return;
            }
            cldr = Calendar.getInstance();
            day = cldr.get(Calendar.DAY_OF_MONTH);
            month = cldr.get(Calendar.MONTH);
            year = cldr.get(Calendar.YEAR);
            dpdEndDate = new DatePickerDialog(this,
                    new DatePickerDialog.OnDateSetListener() {
                        @Override
                        public void onDateSet(DatePicker view, int year, int monthOfYear, int dayOfMonth) {
                            cldr.set(year, monthOfYear, dayOfMonth, 23, 59, 59);
                            endDate = cldr.getTime();
                            btnEndDate.setText(dayOfMonth + "/" + (monthOfYear + 1) + "/" + year);
                            if (hasSelectedStartDate && hasSelectedStaffId) {
                                viewModel.getTransactions(startDate, endDate, selectedStaffId);
                                setupTransactionList();
                            }
                        }
                    }, year, month, day);
            dpdEndDate.show();
            break;
    }
}

The endDate = new Date(year, monthOfYear, dayOfMonth, 23, 59, 59); had a different format so since I was using the "Calendar" instance when saving, I figured that it should have been a "Calendar" instance that I should be passing as parameters.

Cannot get data from SQLite Room Database using dates

1 Answers1