I have a table where I put Date as long and when I convert it back to String it would be "dd/mm/yyyy". I want to fetch certain data with month and year, so the custom query would be like :

SELECT id FROM things 
   WHERE MONTH(happened_at) = 1 AND YEAR(happened_at) = 2009;

the example table has data like this :

id happend_at
-- ----------------
1  2009-01-01 12:08
2  2009-02-01 12:00
3  2009-01-12 09:40
4  2009-01-29 17:55

I'd like to know how to fetch data like that where the happened_at is in long and not String like the example above.

I'm currently learning to make android app using java and room database, so I made a converter to convert Date into long. The converter is like this :

import androidx.room.TypeConverter;
import java.util.Date;  
public class DateConverter {     
    @TypeConverter
         public static Date fromTimestamp(Long value) 
         {         return value == null ? null : 
            new Date(value);     
         }      
    @TypeConverter 
        public static Long dateToTimestamp(Date date) 
        {
                 return date == null ? null : 
                 date.getTime();
        } 
}

CodePudding user response：

Try using :-

SELECT id FROM things WHERE CAST(strftime('%m',happened_at / 1000,'unixepoch') AS INTEGER) = 1 AND CAST(strftime('%Y',happened_at / 1000,'unixepoch') as INTEGER) = 2009;

This works because:-

• the value will be stored as a value that includes the milliseconds according to the TypeConverter as per

• The getTime() method of Java Date class returns the number of milliseconds since January 1, 1970, 00:00:00 GTM which is represented by Date object.

• Dividing by 1000 strips the milliseconds which is then a Unix_Time values (seconds since 00:00:00 on 1st Jan 1970).

• strftime is SQLite's date/time function and can be used for conversion/formatting dates from the recognised types (see link section 2)

The unix_epoch modifier, lets the SQLite strftime function discern between numbers being either Julian Day (without 'unixepoch' modifer) or Unix Time values (with the modifier).

• '%m' retrieves the month as a string padded with preceding 0's to always be 2 characters, '%Y' for the year part.

• see https://www.sqlite.org/lang_datefunc.html

• The CAST expression strips preceding 0's from the result should they exist i.e. converts 01 to 1.

  • an alternative to using CAST could be to force an integer (rather than string) value, say by adding 0 e.g. SELECT id FROM things WHERE 0 strftime('%m',happened_at / 1000,'unixepoch') = 1 AND 0 strftime('%Y',happened_at / 1000,'unixepoch') = 2009;