0

My Table Look Like this:

_id         amount      merchant                category    date        note
15          50000       Mithu Roy               Maintenanc  1473825600  Android
18          7852        efefefwe                Bills       1474084800  efeffwf
21          463         trhth                   Bills       1473912000  thth

I want to calculate the total amount depending upon the month.

Problems:

  1. Dates are in Unix Timestamp.
  2. I've no idea how to get just the month from the UNIX timestamp that's why I can't query the database to get all the month-specific amounts.
Mithu Roy
  • 53
  • 6
  • Check following links:- http://stackoverflow.com/questions/16500527/select-current-months-records-mysql-from-timestamp-column and http://stackoverflow.com/questions/14775012/i-need-the-sqlite-equivalent-of-mysql-for-working-with-unix-timestamps – Anil Ravsaheb Ghodake Sep 19 '16 at 08:50
  • pls go through this [site](http://www.epochconverter.com/) you may get answers – Basi Sep 19 '16 at 09:03
  • Hi, @Anil From this [link](http://stackoverflow.com/questions/16500527/select-current-months-records-mysql-from-timestamp-column) I found this code `SELECT id, FROM_UNIXTIME(timestampfield) timestamp FROM table1 WHERE timestampfield >= UNIX_TIMESTAMP(LAST_DAY(CURDATE()) + INTERVAL 1 DAY - INTERVAL 1 MONTH) AND timestampfield < UNIX_TIMESTAMP(LAST_DAY(CURDATE()) + INTERVAL 1 DAY);` **But it's not working with Android SQLite** – Mithu Roy Sep 19 '16 at 10:14
  • You checked this link:-http://stackoverflow.com/questions/14775012/i-need-the-sqlite-equivalent-of-mysql-for-working-with-unix-timestamps – Anil Ravsaheb Ghodake Sep 19 '16 at 11:08
  • I checked this link before, the problem is I can get the current date but there's no way I can extract the month. If I extract the whole date then I have to process these data in my code itself and get the month and then again query the database to get the month specific amount, it's a multi process system, but I wanted something simple so I can just extract the month directly using SQL and do the query. @Anil – Mithu Roy Sep 19 '16 at 11:59
  • try this query `select * from your_table_name where strftime('%m',date(date,'unixepoch'))=='09'` – Anil Ravsaheb Ghodake Sep 20 '16 at 05:41

0 Answers0