0

I have a table: box (id autoincrement, net_amount, created_at timestamp); I need to create a query in php mysql to select the last inserted record every month. Thus to get the net_amount at the end of every month. I am trying this simple query:

select * from box
 where box.created_at < 20055332516028

While the max created_at in my table is 2017-10-14 10:42:30, there is no records when I use the given query, I need to increase the number to get the records!!!

Shadow
  • 33,525
  • 10
  • 51
  • 64
Galilo Galilo
  • 519
  • 5
  • 22
  • so you have to get record which are inserted during months last two dates right? – Ritesh Khatri Nov 18 '17 at 09:32
  • I need to get the last inserted record within every month. Last record between start and end of every month. – Galilo Galilo Nov 18 '17 at 10:19
  • so you specify the value of start and end? i meam between values right? – Ritesh Khatri Nov 18 '17 at 10:23
  • You can't do it by a single query - at least you need list of month, which is not present now in your tane. Make mysql procedure, temporary table or do it by php and some queries – splash58 Nov 18 '17 at 10:23
  • Yes, I have made the loop. As flying suggested in his answer, I think I still need to recreate the start and end dates, as flying suggested in his answer.I used $startDate = date("Y-m-d", strtotime("2016-01-1")); and $endDate = date("Y-m-d", strtotime("2016-01-1 +" . $i . " month")); then $fromTimeStamp = date('d-m-Y', strtotime($fromDate)); $fromTimeStamp = strtotime($fromTimeStamp); $toTimeStamp = date('d-m-Y', strtotime($toDate)); $toTimeStamp = strtotime($toTimeStamp); – Galilo Galilo Nov 18 '17 at 10:29

2 Answers2

0

if i understand your problem your looking for this:-

SELECT b1.*
FROM box b1 LEFT JOIN box b2
ON (b1.name = b2.name AND b1.id < b2.id)
WHERE b1.created_at < PUT YOUR DATE  and b2.id IS NULL;

Hope it helps

kunal
  • 4,122
  • 12
  • 40
  • 75
0

20055332516028 is not a Unix timestamp. You need to get timestamp of the end of the previous month, something like this:

$date = new DateTime();
$date->setDate($date->format('Y'),$date->format('m'),1);
$date->setTime(0,0,0);
$date->sub(new DateInterval('PT1S'));
$endOfMonth = $date->getTimestamp();

and then use it in a query:

select * from box where box.created_at < unix_timestamp(?) order by box.created_at desc limit 1
Flying
  • 4,422
  • 2
  • 17
  • 25
  • Thank u. I think u r correct, but I still need to specify a starting date, Am I correct? – Galilo Galilo Nov 18 '17 at 10:23
  • I am using a loop with $i to be incremented. and using the following to create the start and end dates in timestamps: $startDate = date("Y-m-d", strtotime("2016-01-1")); $endDate = date("Y-m-d", strtotime("2016-01-1 +" . $i . " month")); $fromTimeStamp = date('d-m-Y', strtotime($fromDate)); $fromTimeStamp = strtotime($fromTimeStamp); $toTimeStamp = date('d-m-Y', strtotime($toDate)); $toTimeStamp = strtotime($toTimeStamp); pls how to specify the date in your solution? – Galilo Galilo Nov 18 '17 at 10:31
  • It really depends on what is inside your table and what information you're trying to get. If it is "last record inserted at specified month" then it is basically "sort all records by reverse date, filter out ones that are after end of specified month and take first one" and it is what my proposed query is for. – Flying Nov 18 '17 at 10:32
  • @GaliloGalilo I've updated my answer by adding use of `unix_timestamp()` function in a query. It is important because dealing with dates correctly should involve timezone definition and your database stores dates as timestamps. Also you may want to bring your dates to same timezone, e.g. `UTC` – Flying Nov 18 '17 at 10:36
  • So I think I need a method to begin from a starting date and get the end of month, then the end of the next month and so on till the current date. I think I need to change the requirements!! :( sorry. – Galilo Galilo Nov 18 '17 at 10:42
  • For date specification you need to refer [`DateTime`](https://secure.php.net/manual/en/book.datetime.php) object specification. Basically you can use `$date->add(new DateInterval('P1M1D'))` to certainly move date to next month and then tune it to the "last second of the month" like it is shown in my example – Flying Nov 18 '17 at 10:42