Android SQLite: Datetime between query

Question

I am using the following code to store my date in my database:

String mydate = java.text.DateFormat.getDateTimeInstance().format(
                Calendar.getInstance().getTime());

This is a string in the db. There are multiple lists and I want to display to the user changes made based on the day.

In sqlserver there is an actual date variable and you can run BETWEEN on it, I am not sure how to implement this with the current datatype i'm using.

There may be a better way to store the date, but this was the only way that I have been able to successfully do it.

Simon Dorociak · Accepted Answer · 2013-04-28 17:26:08Z

2

This is a string in the db. There are multiple lists and I want to display to the user changes made based on the day.

Your actual code you can improve with defining custom Date pattern. You can use to achieve it for example SimpleDateFormat and then store Date as String with your defined pattern.

There are multiple lists and I want to display to the user changes made based on the day.

So first idea that came to my head is when you exactly know in which format you are storing your dates, you can simply use substr() function to get for example only day from a whole Date and then compare only days. Equivalent will be comparing only months, etc.

For tests if you specified correct pattern for SimpleDateFormat you can use:

Common Pattern Strings for java.text.SimpleDateFormat

answered Apr 28, 2013 at 17:26

Simon Dorociak

33.5k10 gold badges70 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

323go · Accepted Answer · 2013-04-28 17:22:12Z

1

If you're storing the date as a string, you'll need to specify a "comparable" format, such as YYYY-MM-DD HH:nn:ss, or, shorter, YYYYMMDDHHnnSS. You might be much better off storing the date as an integer epoch though (seconds since 1970). This is especially true if you're indexing the column and running comparative queries against it.

answered Apr 28, 2013 at 17:22

323go

14.3k6 gold badges37 silver badges41 bronze badges

1 Comment

Zach M. Over a year ago

This may be the way to go, all of my queries are running against the date column. An integer value should be much easier to write queries against. I will look into this.

Collectives™ on Stack Overflow

Android SQLite: Datetime between query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related