• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 718
  • Last Modified:

Mysql Query to retrieve data from last month range

HI,

I have the attached MySql Query, "post.dateline" is the date field. I want to add a WHERE clause to get only the records 40 days old or newer.

How to say?

Thanks
SELECT thread.dateline AS dateline_1, thread.threadid, thread.title, thread.lastpostid, thread.forumid, post.username, post.userid, post.dateline, thread.views, post.pagetext, thread.postusername, thread.replycount
FROM (thread LEFT JOIN post ON post.postid=thread.lastpostid)

Open in new window

0
Fernanditos
Asked:
Fernanditos
  • 4
  • 3
  • 2
  • +1
1 Solution
 
KalpanCommented:
SELECT thread.dateline AS dateline_1, thread.threadid, thread.title, thread.lastpostid, thread.forumid, post.username, post.userid, post.dateline, thread.views, post.pagetext, thread.postusername, thread.replycount
FROM (thread LEFT JOIN post ON post.postid=thread.lastpostid)
WHERE
post.dateline < NOW() - INTERVAL 40 DAY


0
 
kingjelyCommented:

< I want to add a WHERE clause to get only the records 40 days old or newer.



SELECT thread.dateline AS dateline_1, thread.threadid, thread.title, thread.lastpostid, thread.forumid, post.username, post.userid, post.dateline, thread.views, post.pagetext, thread.postusername, thread.replycount
FROM (thread LEFT JOIN post ON post.postid=thread.lastpostid)
WHERE post.dateline between (NOW() - INTERVAL 40 DAY) and (NOW() + INTERVAL 40 DAY)
0
 
FernanditosAuthor Commented:
Both solutions are not working:

I forgot to say that "dateline" date-time format looks like:

1268262191 on database (that would be March 27, 2010, 11:37 am)

Any idea?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
KalpanCommented:
ELECT thread.dateline AS dateline_1, thread.threadid, thread.title, thread.lastpostid, thread.forumid, post.username, post.userid, post.dateline, thread.views, post.pagetext, thread.postusername, thread.replycount
FROM (thread LEFT JOIN post ON post.postid=thread.lastpostid)
WHERE
DATE_FORMAT(post.dateline,'%d/%m/%Y') < NOW() - INTERVAL 40 DAY
0
 
kingjelyCommented:


what timestamp is that?

0
 
FernanditosAuthor Commented:
"DATE_FORMAT(post.dateline,'%d/%m/%Y') < NOW() - INTERVAL 40 DAY" returns non rows, even if there are rows from 1 day old.

The "dateline" filed is a vbulletin standard date/time format. in the database is an int(10)
0
 
kingjelyCommented:

Hrrmm..

what about

SELECT thread.dateline AS dateline_1, thread.threadid, thread.title, thread.lastpostid, thread.forumid, post.username, post.userid, post.dateline, thread.views, post.pagetext, thread.postusername, thread.replycount
FROM (thread LEFT JOIN post ON post.postid=thread.lastpostid)
WHERE
DATE_FORMAT(post.dateline,'%d/%m/%Y') between NOW() and NOW() - INTERVAL 40 DAY
0
 
FernanditosAuthor Commented:
still does not works.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, you want something like this:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_unix-timestamp
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add


SELECT thread.dateline AS dateline_1, thread.threadid, thread.title, thread.lastpostid, thread.forumid, post.username, post.userid, post.dateline, thread.views, post.pagetext, thread.postusername, thread.replycount
FROM (thread LEFT JOIN post ON post.postid=thread.lastpostid)
WHERE post.dateline >= UNIX_TIMESTAMP( DATE_SUB(NOW() , INTERVAL 40 DAY) )
  AND post.dateline <= UNIX_TIMESTAMP( DATE_ADD(NOW() , INTERVAL 40 DAY) )

Open in new window

0
 
kingjelyCommented:
When i checked the UNIX_TIMESTAMP for '2010-03-27 11:37'
(like your comment said, 1268262191 on database (that would be March 27, 2010, 11:37 am)

UNIX_TIMESTAMP('2010-03-27 11:37')  returns 1269661020 NOT 1268262191

I wonder why? I don't understand
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now