Solved

Mysql Query to retrieve data from last month range

Posted on 2010-08-19
10
700 Views
Last Modified: 2013-12-13
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
Comment
Question by:Fernanditos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 14

Expert Comment

by:Kalpan
ID: 33472838
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
 
LVL 8

Expert Comment

by:kingjely
ID: 33472858

< 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
 

Author Comment

by:Fernanditos
ID: 33473041
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
Industry Leaders: 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!

 
LVL 14

Expert Comment

by:Kalpan
ID: 33473129
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
 
LVL 8

Expert Comment

by:kingjely
ID: 33473137


what timestamp is that?

0
 

Author Comment

by:Fernanditos
ID: 33473372
"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
 
LVL 8

Expert Comment

by:kingjely
ID: 33473723

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
 

Author Comment

by:Fernanditos
ID: 33474324
still does not works.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 33474652
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
 
LVL 8

Expert Comment

by:kingjely
ID: 33481522
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Call to SQL server times out 5 55
Format Date 7 28
Format dates for use with Progress Database from Pyhton 1 27
sitemap.xml 3 28
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question