Hello everyone, there is an application where in the SQLite database there is a column "date" with the type "text" (not sure what the correct type is) where the date is stored in the format 01/12/2016. It is necessary to make a selection for the length of time. For example, from 01/05/2016 to 12/01/2016. Find by date is not a problem, but how to make a sample for a period of time?

  • one
    If possible, it is better to make the type INTEGER and store the time stamp. - temq
  • Is this type in a non converted form? And how to do the sample? More than INTEGER number? - Eugene Zaychenko
  • Well, yes, timestemp is the usual number, which represents the number of seconds since January 1, 1970, so in the database they can be stored as a number, and the sample, respectively, can be made smaller through the usual comparison conditions. You can also convert this number to a human-readable format for display to the user without any problems - temq
  • And from String in the form "01/01/2016" - can I get a number in some way? Convert to long (number of seconds)? timestemp is what? I get using System.currentTimeMillis () and then convert through SimpleDateFormat - Eugene Zaychenko
  • timestamp is what System.currentTimeMillis() returns, only in milliseconds. Regarding the conversion, look for more than one such question asked here, or see for yourself SimpleDateFormat - temq

1 answer 1

This information is not directly related to sampling by range (except casting to the timestring format), but it will be very useful when working with dates in SQLite.

To work with dates in SQLite, you need to bring this date to the format that this database can handle - timestring of the form YYYY-MM-DD [HH: MM: SS].

Further, using SQL functions (for example, date() or strfdate() ) you can get specific values ​​for making queries or directly get certain data (basically, how much time has passed). Official documentation on this issue and translation in Russian for poor readers.

In addition, you can use the timestamp format (unixtime, number of seconds since 1970). When using this format, you must add the unixepoch modifier to the date() unixepoch , which indicates that the date is in the timestamp, not the timestring. This solution has the advantage that in addition to the special functions of SQLite, you can work with dates as with a prime number (the larger the number, the more time / older the date)

The request for sampling by date range can be as follows:

 select * from datetable where date between '2015-12-11' and '2016-12-11'

UPD:

To convert from unixtime to a "human" type of date, use the SimpleDateFormat class (in this example, the data is taken from the column of the cursor named date ):

 long unixTime = cursor.getLong(getColumnIndex("date")); Date date = new Date(unixTime*1000L); // переводим в формат Android (см. примечание) SimpleDateFormat sdf = new SimpleDateFormat("dd.mm.yyyy"); //шаблон для даты в текстовом виде - получится так: 26.12.2016 sdf.setTimeZone(TimeZone.getTimeZone("GMT+4")); //если требуется, устанавливаем таймзону String formattedDate = sdf.format(date); textView.setText(formattedDate); // выводим в текствью

You can place this code directly in the adapter (and in any other class) and convert the data from the cursor (or any other data source) before displaying it on the screen. If you use one of the standard adapters, for example, SimpleCursorAdapter , then to implement the output you will need to write your own custom adapter based on the one used (inherited from it), which will perform this conversion.

Note: In Android, the time management classes return the timestamp in milliseconds , and in SQLite, the date() function takes it in seconds , so the value needs to be converted (multiplied by 1000).

  • If I save to Unix, and then using the SimpleCursorAdapter I want to display it in a ListView, how can I convert it to the desired date format? The adapter takes values ​​directly from the database. - Eugene Zaychenko