Best way to work with dates in Android SQLite

java

date-manipulation

sqlite-best-practices

android-development

byAnton Shumikhin·Jan 30, 2025

Optimal handling of dates in Android SQLite involves using the TEXT format in ISO8601 ('YYYY-MM-DD HH:MM:SS'). It's best to manipulate dates using SQLite's date functions like strftime(). To ensure fast operations, remember to index your date column.

Here's a SQL example with a nice touch of humor:

-- Table with an indexed date column
CREATE TABLE events (id INTEGER PRIMARY KEY, name TEXT, event_date TEXT);
CREATE INDEX idx_event_date ON events (event_date);

-- ISO8601 date insertion. The party starts now!
INSERT INTO events (name, event_date) VALUES ('Party', '2023-04-01 14:30');

-- Select events within a date range. Let's see who else is partying this month...
SELECT * FROM events WHERE event_date BETWEEN '2023-04-01' AND '2023-04-30';

To safeguard against SQL injection, always use parameterized queries and SimpleDateFormat in Java for working with dates.

Getting current time and storing as long

In Android, obtaining the current time in milliseconds should always go through System.currentTimeMillis(). This long timestamp gives you precise and consistent datetimes. It's ideal for storing date values in SQLite, as it allows efficient querying and sorting.

// Getting current time in milliseconds...tic tac tic tac
long currentTime = System.currentTimeMillis();

Handling date fetching and storing

Successfully managing dates involves retrieving and formatting them in a way that highlights both human and machine usability. For retrieving timestamps from SQLite, make sure to use the cursor.getLong() method.

Once you have the date, you can format it according to your user's locale using android.text.format.DateUtils. To store date values, it's typically best to stick with a long data type in SQLite because it enables more efficient operations.

// Let's get the date from SQLite...no time to wait!
long dateValue = cursor.getLong(cursor.getColumnIndex("event_date"));

// Now, let's make it more human-friendly...because we all love friendly human(?)
String formattedDate = DateUtils.formatDateTime(context, dateValue, DateUtils.FORMAT_SHOW_DATE);

Adjusting for timezones

In SQLite, it's safer to store dates in UTC format. This avoids any potential timezone-related issues. Then, you can account for the timezone conversion when displaying the dates to users.

Utility methods to make your life easier

Utility methods lightning the load when you need to convert date to/from milliseconds. They keep your code organized and promote consistent date manipulation across your entire project.

// Let the utility methods perform their magic...time-ly magic!
long timeInMilliseconds = convertDateToMilliseconds(date);
Date date = convertMillisecondsToDate(timeInMilliseconds);

Sorting, querying, and beyond

To stone two birds with one SQL query, learn how to use the ORDER BY clause to efficiently sort data by date and the BETWEEN clause for neat date range comparisons.

// Retrieve events ordered by date, because order matters...
SELECT * FROM events ORDER BY event_date;

// Get all the parties happening between these dates, let's not miss out!
SELECT * FROM events WHERE event_date BETWEEN '2023-04-01' AND '2023-04-30';

Embracing the power of serialization

Leverage the java.util.Date and java.util.Calendar classes to manipulate dates and handle conversions to numeric timestamps, thereby assuring a flexible and customer-oriented app.

Adapting to DATAtypes

(SQL)ite your storage preferences, use SQLite's TEXT, REAL, or INTEGER data types to store date values. ContentValues can help you insert dates safely into SQLite, while SQLite's own date/time functions equip you to manipulate those dates.

Android and SQLite

Data integrity, security and the integration of Android's Room Persistence Library come into focus when considering SQLite's role in Android development.

explain-codes / Java / Best way to work with dates in Android SQLite

Linked

How to create timestamp column with default value 'now'?



How do I insert datetime value into a SQLite database?



Sqlite DateTime comparison



Using setDate in PreparedStatement



How to convert java.util.Date to java.sql.Date?

