How to get the last month of data in database?

Question

I need the last month of recorded data, not the data from current date -1 month.

Here my wrong query:D

SELECT  MIN(date(Timestamp)) as fDate , MAX(date(Timestamp)) as tDate  FROM data WHERE boxid=45 AND
 YEAR(Timestamp) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(Timestamp) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)

EDIT: i have much data in my table, i just want to see where the last recoded data starts and where it ends, but i want the last month.

example , table has recoded till january 17th. i want now 2 dates: January 1st and 17th

I tried this

SELECT max(date(Timestamp)) as toDate, min(date(timestamp)) as fromDate FROM data WHERE timestamp<=(SELECT MAX(date(Timestamp)) FROM data )
AND timestamp>=(SELECT (MAX(date(Timestamp))- INTERVAL 1 MONTH) FROM data ) AND ID=10

it has a strange behavior , its giving me not 30 days...

yes i need the last month, actually only the 2 dates, where the last months data start and where it ends, thats why i used max() and min() — Khan, Sep 16 '16 at 11:48
http://stackoverflow.com/questions/2041575/mysql-query-records-between-today-and-last-30-days ? — VirginieLGB, Sep 16 '16 at 11:50
no , because its from current day, till last 30 days. I need only last 30 days that can be 1899 january1st- 1899 january 31th — Khan, Sep 16 '16 at 11:57
@Khan, If i am not wrong, you have the data with dates like "2016-08-01", "2016-08-13","2016-08-17", "2016-09-01", "2016-09-02","2016-09-03". Now you want to select the records where date is matching with "2016-08-01", "2016-08-13","2016-08-17". Is it correct? — Sanjay Reddy Addula, Sep 16 '16 at 12:34
Then you need to do a first select to get the last timestamp and use this instead of CURRENT_DATE — VirginieLGB, Sep 16 '16 at 12:49
yes that sounds right how can i use this instead of CURRENT_DATE — Khan, Sep 16 '16 at 12:53

VirginieLGB · Answer 1 · 2016-09-16T13:15:37.143

1

Alright so to try this I created a table called 'recordings' which only contains id (int) and timestamp (timestamp).

Here's the content of the table:

SELECT * FROM `recordings` WHERE `timestamp` <= ( SELECT `timestamp` FROM `recordings` ORDER BY `timestamp` DESC LIMIT 1 ) AND `timestamp` >= (( SELECT `timestamp` FROM `recordings` ORDER BY `timestamp` DESC LIMIT 1 ) - INTERVAL 1 MONTH))

And I got this: Which is exactly the recordings from 1 month before the last one

edited Sep 16 '16 at 13:15

answered Sep 16 '16 at 13:05

VirginieLGB

548
2
15

thats what i need but its giving me a syntax error :( #1064 – Khan Sep 16 '16 at 13:14
Can you copy and paste your exact query please? – VirginieLGB Sep 16 '16 at 13:15
i copied your query and replaced recordings with data – Khan Sep 16 '16 at 13:19
1

What about timestamp? Have you changed the case? Also I forgot ticks (`) in my answer, that may cause errors depending on your version of MySQL - I have corrected it since – VirginieLGB Sep 16 '16 at 13:21

score 0 · Answer 2 · answered Sep 16 '16 at 14:24

The first day of the last available month of data is given by:

MAX(DATE(`Timestamp`)) - INTERVAL (DAYOFMONTH(`Timestamp`) - 1 ) DAY
-- e.g., 2016-09-16 - INTERVAL (16 - 1) DAY => 2016-09-01

and the latest available day is simply

MAX(DATE(`Timestamp`))

So you can

SELECT last_day - INTERVAL (DAYOFMONTH(last_day) - 1) DAY AS first_day,
       last_day
  FROM (SELECT MAX(DATE(`Timestamp`)) AS last_day FROM data) derived;

(You don't need that derived table, of course, but I find that more readable than repeating max(date(`blah`)) over and over.)

score -1 · Answer 3 · edited Sep 16 '16 at 12:54

-1

SELECT * 
FROM Member
WHERE DATEPART(m, date_created) = DATEPART(m, DATEADD(m, -1, getdate()))
AND DATEPART(yyyy, date_created) = DATEPART(yyyy, DATEADD(m, -1, getdate()))

edited Sep 16 '16 at 12:54

TobiasR.

826
9
19

answered Sep 16 '16 at 11:50

Dipankar Samanta

21
3

2

Some explanation would be great. – TobiasR. Sep 16 '16 at 11:55
I didn't downtick you but I think it is because this doesn't allow you to fetch any month, only last month. I personally would use your answer. It's the simplest. I'll uptick you :) – Missy Sep 16 '16 at 14:34

How to get the last month of data in database?

3 Answers3