Problem
On my Android application that uses SQLite, I’m having some issues working with dates. I’d want to ask you a couple of questions:
Asked by Filipe
Solution #1
The most efficient method is to save the dates as a number, which may be obtained by using the Calendar command.
//Building the table includes:
StringBuilder query=new StringBuilder();
query.append("CREATE TABLE "+TABLE_NAME+ " (");
query.append(COLUMN_ID+"int primary key autoincrement,");
query.append(COLUMN_DATETIME+" int)");
//And inserting the data includes this:
values.put(COLUMN_DATETIME, System.currentTimeMillis());
Why are you doing this? To begin with, obtaining values from a date range is simple. Simply convert your date to milliseconds and query accordingly. Sorting by date is also simple. As I mentioned, the calls to convert between different formats are similarly simple. The bottom line is that you can do anything you want with this strategy. It will be slightly more difficult to read a raw value, but the ease with which it may be read and used by machines more than compensates for this minor disadvantage. In fact, it’s quite simple to create a reader (and I’m sure there are others out there) that converts the time tag to date format for easy reading.
It’s worth noting that the values generated should be long rather than int. In sqlite, an integer can be anything from 1 to 8 bytes, although for virtually all dates, 64 bits, or a long, works best.
EDIT: As previously stated in the comments, you must utilize the cursor. If you do this, you’ll need to use getLong() to get the timestamp correctly.
Answered by PearsonArtPhoto
Solution #2
In SQLite, you can store dates in a text field.
If you use datetime(‘now’) (yyyy-MM-dd HH:mm:ss) to save dates in UTC format, you’ll be able to sort by the date column.
After retrieving dates as strings from SQLite, you can use the Calendar or the android.text.format.DateUtils.formatDateTime method to format/convert them into local regionalised formats as needed.
Here’s how I use a regionalized formatter:
public static String formatDateTime(Context context, String timeToFormat) {
String finalDateTime = "";
SimpleDateFormat iso8601Format = new SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss");
Date date = null;
if (timeToFormat != null) {
try {
date = iso8601Format.parse(timeToFormat);
} catch (ParseException e) {
date = null;
}
if (date != null) {
long when = date.getTime();
int flags = 0;
flags |= android.text.format.DateUtils.FORMAT_SHOW_TIME;
flags |= android.text.format.DateUtils.FORMAT_SHOW_DATE;
flags |= android.text.format.DateUtils.FORMAT_ABBREV_MONTH;
flags |= android.text.format.DateUtils.FORMAT_SHOW_YEAR;
finalDateTime = android.text.format.DateUtils.formatDateTime(context,
when + TimeZone.getDefault().getOffset(when), flags);
}
}
return finalDateTime;
}
Answered by CodeChimp
Solution #3
When working with java.util.Calendar and java.text, always remember to save the UTC/GMT time. SimpleDateFormat that uses your device’s default time zone. java.util.Date. Because Date() generates a UTC value, it is safe to utilize.
Answered by schnatterer
Solution #4
To store dates, SQLite can employ text, real, or integer data types. Furthermore, the results of any query are displayed in the format percent Y- percent m- percent d percent H: percent M: percent S.
If you use SQLite date/time functions to insert/update date/time information, you may now save milliseconds as well. If this is the case, the findings are displayed in percent Y- percent m- percent d percent H: percent M: percent f format. Consider the following scenario:
sqlite> create table test_table(col1 text, col2 real, col3 integer);
sqlite> insert into test_table values (
strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.123'),
strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.123'),
strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.123')
);
sqlite> insert into test_table values (
strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.126'),
strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.126'),
strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.126')
);
sqlite> select * from test_table;
2014-03-01 13:01:01.123|2014-03-01 13:01:01.123|2014-03-01 13:01:01.123
2014-03-01 13:01:01.126|2014-03-01 13:01:01.126|2014-03-01 13:01:01.126
Now, let’s run some queries to see whether we can truly compare times:
sqlite> select * from test_table /* using col1 */
where col1 between
strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.121') and
strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.125');
2014-03-01 13:01:01.123|2014-03-01 13:01:01.123|2014-03-01 13:01:01.123
You can use col2 and col3 to check the same SELECT and get the same results. The second row (126 milliseconds) is not returned, as you can see.
It’s worth noting that BETWEEN is inclusive, therefore…
sqlite> select * from test_table
where col1 between
/* Note that we are using 123 milliseconds down _here_ */
strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.123') and
strftime('%Y-%m-%d %H:%M:%f', '2014-03-01 13:01:01.125');
… will give you the same results.
Everything will operate as expected if you experiment with different date/time ranges.
What if the strftime function isn’t available?
sqlite> select * from test_table /* using col1 */
where col1 between
'2014-03-01 13:01:01.121' and
'2014-03-01 13:01:01.125';
2014-03-01 13:01:01.123|2014-03-01 13:01:01.123|2014-03-01 13:01:01.123
What if there was no milliseconds and no strftime function?
sqlite> select * from test_table /* using col1 */
where col1 between
'2014-03-01 13:01:01' and
'2014-03-01 13:01:02';
2014-03-01 13:01:01.123|2014-03-01 13:01:01.123|2014-03-01 13:01:01.123
2014-03-01 13:01:01.126|2014-03-01 13:01:01.126|2014-03-01 13:01:01.126
Isn’t ORDER BY a good option?
sqlite> select * from test_table order by 1 desc;
2014-03-01 13:01:01.126|2014-03-01 13:01:01.126|2014-03-01 13:01:01.126
2014-03-01 13:01:01.123|2014-03-01 13:01:01.123|2014-03-01 13:01:01.123
sqlite> select * from test_table order by 1 asc;
2014-03-01 13:01:01.123|2014-03-01 13:01:01.123|2014-03-01 13:01:01.123
2014-03-01 13:01:01.126|2014-03-01 13:01:01.126|2014-03-01 13:01:01.126
Works just fine.
Finally, when it comes to the actual operations of a programme (without using the sqlite executable…)
BTW: I’m using JDBC (not sure about other languages)… the sqlite-jdbc driver v3.7.2 from xerial – Perhaps newer updates will alter the behavior described here… If you are developing in Android, you don’t need a jdbc-driver. All SQL operations can be submitted using the SQLiteOpenHelper.
JDBC has different methods to get actual date/time values from a database: java.sql.Date, java.sql.Time, and java.sql.Timestamp.
The related methods in java.sql.ResultSet are (obviously) getDate(..), getTime(..), and getTimestamp() respectively.
For example:
Statement stmt = ... // Get statement from connection
ResultSet rs = stmt.executeQuery("SELECT * FROM TEST_TABLE");
while (rs.next()) {
System.out.println("COL1 : "+rs.getDate("COL1"));
System.out.println("COL1 : "+rs.getTime("COL1"));
System.out.println("COL1 : "+rs.getTimestamp("COL1"));
System.out.println("COL2 : "+rs.getDate("COL2"));
System.out.println("COL2 : "+rs.getTime("COL2"));
System.out.println("COL2 : "+rs.getTimestamp("COL2"));
System.out.println("COL3 : "+rs.getDate("COL3"));
System.out.println("COL3 : "+rs.getTime("COL3"));
System.out.println("COL3 : "+rs.getTimestamp("COL3"));
}
// close rs and stmt.
Due to the lack of a DATE/TIME/TIMESTAMP data type in SQLite, these three methods return values as if the objects were initialized with 0:
new java.sql.Date(0)
new java.sql.Time(0)
new java.sql.Timestamp(0)
So, how do we go about selecting, inserting, or updating Date/Time/Timestamp objects? There is no simple solution. You can experiment with different combinations, but they will all need you to use SQLite functions in all SQL statements. It’s significantly more straightforward to create a utility class that converts text to Date objects within your Java program. But keep in mind that SQLite converts all date values to UTC+0000.
In conclusion, despite the general rule of always using the correct data type, or even integers denoting Unix time (milliseconds since epoch), I find it much easier to use the default SQLite format (‘percent Y-percent m-percent d percent H:percent M:percent f’ or in Java ‘yyyy-MM-dd HH:mm:ss.SSS’) rather than complicating all your SQL statements with The former method is considerably easier to keep up with.
TODO: I’ll see what happens if I use getDate/getTime/getTimestamp inside Android (API15 or higher)… maybe the internal driver isn’t the same as sqlite-jdbc…
Answered by miguelt
Solution #5
I usually store dates in int(mysql/post) or text(sqlite) to store them in the timestamp format (like I do in mysql/postgres).
Then, based on the user’s TimeZone, I’ll convert them to Date objects and conduct actions on them.
Answered by StErMi
Post is based on https://stackoverflow.com/questions/7363112/best-way-to-work-with-dates-in-android-sqlite