Best way to work with dates in Android SQLite

Question

I m having some trouble working with dates on my Android application that uses SQLite  I have a couple questions    What type should I use to store dates in SQLite  text  integer        Given the best way to store dates how do I store It properly using ContentValues  What s the best way to retrieve the date from the SQLite database  How to make a sql select on SQLite  ordering the results by date

User · Answer

SQLite can use text  real  or integer data types to store dates  Even more  whenever you perform a query  the results are shown using format  Y- m- d  H  M  S   Now  if you insert update date time values using SQLite date time functions  you can actually store milliseconds as well  If that s the case  the results are shown using format  Y- m- d  H  M  f  For example   sqlite gt  create table test table col1 text  col2 real  col3 integer   sqlite gt  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 gt  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 gt  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  doing some queries to verify if we are actually able to compare times   sqlite gt  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 check the same SELECT using col2 and col3 and you will get the same results  As you can see  the second row  126 milliseconds  is not returned    Note that BETWEEN is inclusive  therefore     sqlite gt  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 return the same set   Try playing around with different date time ranges and everything will behave as expected   What about without strftime function   sqlite gt  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 about without strftime function and no milliseconds   sqlite gt  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   What about ORDER BY   sqlite gt  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 gt  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 dealing with actual operations within a program  without using the sqlite executable      BTW  I m using JDBC  not sure about other languages     the sqlite-jdbc driver v3 7 2 from xerial - maybe newer revisions change the behavior explained below    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    Since SQLite doesn t have an actual DATE TIME TIMESTAMP data type all these 3 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  the question is  how can we actually select  insert  or update Date Time Timestamp objects  There s no easy answer  You can try different combinations  but they will force you to embed SQLite functions in all the SQL statements  It s far easier to define an utility class to transform text to Date objects inside your Java program  But always remember that SQLite transforms any date value to UTC 0000   In summary  despite the general rule to always use the correct data type  or  even integers denoting Unix time  milliseconds since epoch   I find much easier using the default SQLite format    Y- m- d  H  M  f  or in Java  yyyy-MM-dd HH mm ss SSS   rather to complicate all your SQL statements with SQLite functions  The former approach is much easier to maintain   TODO  I will check the results when using getDate getTime getTimestamp inside Android  API15 or better     maybe the internal driver is different from sqlite-jdbc

User · Answer

I prefer this  This is not the best way  but a fast solution     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 CREATION DATE   DATE        Inserting the data includes this  SimpleDateFormat dateFormat   new SimpleDateFormat  yyyy-MM-dd HH mm ss SSS    values put COLUMN CREATION DATE dateFormat format reactionGame getCreationDate           Fetching the data includes this  try      java util Date creationDate   dateFormat parse cursor getString 0      YourObject setCreationDate creationDate      catch  Exception e       YourObject setCreationDate null

User · Answer

You can use a text field to store dates within SQLite   Storing dates in UTC format  the default if you use datetime  now    yyyy-MM-dd HH mm ss  will then allow sorting by the date column   Retrieving dates as strings from SQLite you can then format convert them as required into local regionalised formats using the Calendar or the android text format DateUtils formatDateTime method   Here s a regionalised formatter method I use   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

User · Answer

Best way to store datein SQlite DB is to store the current DateTimeMilliseconds  Below is the code snippet to do so         Get the DateTimeMilliseconds      public static long getTimeMillis String dateString  String dateFormat  throws ParseException         Use date format as according to your need  Ex  - yyyy MM dd HH mm ss        String myDate   dateString    2017 12 20 18 10 45       SimpleDateFormat sdf   new SimpleDateFormat dateFormat   yyyy MM dd HH mm ss          Date date   sdf parse myDate       long millis   date getTime         return millis            Insert the data in your DB      public void insert Context mContext  long dateTimeMillis  String msg          Your DB Helper     MyDatabaseHelper dbHelper   new MyDatabaseHelper mContext       database   dbHelper getWritableDatabase         ContentValues contentValue   new ContentValues        contentValue put MyDatabaseHelper DATE MILLIS  dateTimeMillis       contentValue put MyDatabaseHelper MESSAGE  msg          insert data in DB     database insert  your table name   null  contentValue         Close the DB connection     dbHelper close          Now  your data  date is in currentTimeMilliseconds  is get inserted in DB    Next step is  when you want to retrieve data from DB you need to convert the respective date time milliseconds in to corresponding date  Below is the sample code snippet to do the same         Convert date milliseconds in to date string        public static String getDate long milliSeconds  String dateFormat           Create a DateFormatter object for displaying date in specified format      SimpleDateFormat formatter   new SimpleDateFormat dateFormat   yyyy MM dd HH mm ss              Create a calendar object that will convert the date and time value in milliseconds to date      Calendar calendar   Calendar getInstance        calendar setTimeInMillis milliSeconds       return formatter format calendar getTime               Now  Finally fetch the data and see its working          public ArrayList lt String gt  fetchData          ArrayList lt String gt  listOfAllDates   new ArrayList lt String gt         String cDate   null       MyDatabaseHelper dbHelper   new MyDatabaseHelper  your app context        database   dbHelper getWritableDatabase         String   columns   new String    MyDatabaseHelper DATE MILLIS  MyDatabaseHelper MESSAGE       Cursor cursor   database query  your table name   columns  null  null  null  null  null        if  cursor    null             if  cursor moveToFirst                 do                    iterate the cursor to get data                  cDate   getDate cursor getLong cursor getColumnIndex MyDatabaseHelper DATE MILLIS     yyyy MM dd HH mm ss                     listOfAllDates add cDate                 while cursor moveToNext                       cursor close           Close the DB connection      dbHelper close          return listOfAllDates       Hope this will help all

User · Answer

SELECT     ID         DESCRIPTION       CREATED DATE       DATE TIME   FROM   TBL NOTIFICATION   ORDER BY    strftime  s    DATE TIME    DESC

User · Answer

1 -Exactly like StErMi said   2 - Please read this  http   www vogella de articles AndroidSQLite article html  3 -   Cursor cursor   db query TABLE NAME  new String      id    title    title raw    timestamp                           YOUR REQUEST       null  null   timestamp   null     see here    Query   in SQLiteDatabase  4 - see answer 3

User · Answer

The best way is to store the dates as a number  received 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 do this  First of all  getting values from a date range is easy  Just convert your date into milliseconds  and then query appropriately  Sorting by date is similarly easy  The calls to convert among various formats are also likewise easy  as I included  Bottom line is  with this method  you can do anything you need to do  no problems  It will be slightly difficult to read a raw value  but it more than makes up that slight disadvantage with being easily machine readable and usable  And in fact  it is relatively easy to build a reader  And I know there are some out there  that will automatically convert the time tag to date as such for easy of reading   It s worth mentioning that the values that come out of this should be long  not int  Integer in sqlite can mean many things  anything from 1-8 bytes  but for almost all dates 64 bits  or a long  is what works   EDIT  As has been pointed out in the comments  you have to use the cursor getLong   to properly get the timestamp if you do this

User · Answer

As presumed in this comment  I d always use integers to store dates  For storing  you could use a utility method  public static Long persistDate Date date        if  date    null            return date getTime              return null      like so   ContentValues values   new ContentValues    values put COLUMN NAME  persistDate entity getDate      long id   db insertOrThrow TABLE NAME  null  values    Another utility method takes care of the loading  public static Date loadDate Cursor cursor  int index        if  cursor isNull index             return null            return new Date cursor getLong index        can be used like this   entity setDate loadDate cursor  INDEX     Ordering by date is simple SQL ORDER clause  because we have a numeric column   The following will order descending  that is newest date goes first    public static final String QUERY    SELECT table  id  table dateCol FROM table ORDER BY table dateCol DESC               Cursor cursor   rawQuery QUERY  null       cursor moveToFirst         while   cursor isAfterLast                 Process results          Always make sure to store the UTC GMT time  especially when working with java util Calendar and java text SimpleDateFormat that use the default  i e  your device s  time zone   java util Date Date   is safe to use as it creates a UTC value

User · Answer

Usually  same as I do in mysql postgres  I stores dates in int mysql post  or text sqlite  to store them in the timestamp format   Then I will convert them into Date objects and perform actions based on user TimeZone

[android] Best way to work with dates in Android SQLite

Examples related to android

Examples related to sql

Examples related to database

Examples related to sqlite

Examples related to date