9 Replies - 12126 Views - Last Post: 16 April 2012 - 07:11 PM

#1 Symbiot  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 28-May 10

sql query -show rows 7 days or less old from today's date

Posted 16 April 2012 - 06:03 AM

Hi guys

Pretty much as topic.

is there a way to write a sql query that automatically looks at todays date and then shows the rows in a table that are either todays date or a max of 7 days older.

This is for a forum query that will show topics from the last week.

There is a column with posted dates.

I can do this
SELECT * FROM jos_agora_topics 
WHERE posted <=  UNIX_TIMESTAMP()



Then I guess I need

AND WHERE posted >= UNIX_TIMESTAMP(-7 days)

but how?

Hoping for some great insights!!

Is This A Good Question/Topic? 0
  • +

Replies To: sql query -show rows 7 days or less old from today's date

#2 Atli  Icon User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4240
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: sql query -show rows 7 days or less old from today's date

Posted 16 April 2012 - 06:07 AM

Hey.

You can do something like this:
WHERE posted > NOW() - INTERVAL 7 DAY



It's pretty much the same as using the DATE_SUB function.
Was This Post Helpful? 1
  • +
  • -

#3 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 593
  • View blog
  • Posts: 3,823
  • Joined: 12-January 10

Re: sql query -show rows 7 days or less old from today's date

Posted 16 April 2012 - 06:23 AM

you can also use the getdate() to get current date in sql
Was This Post Helpful? 0
  • +
  • -

#4 Atli  Icon User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4240
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: sql query -show rows 7 days or less old from today's date

Posted 16 April 2012 - 06:31 AM

View PostDarenR, on 16 April 2012 - 01:23 PM, said:

you can also use the getdate() to get current date in sql

That function doesn't exist in MySQL. It's used in MSSQL and Oracle, if I am not mistaken. The equivelent for MySQL would be NOW()

Edit: Actually, looking closer at the MSDN docs, the UTC_TIMESTAMP() function would be a closer match.

This post has been edited by Atli: 16 April 2012 - 06:36 AM

Was This Post Helpful? 0
  • +
  • -

#5 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 593
  • View blog
  • Posts: 3,823
  • Joined: 12-January 10

Re: sql query -show rows 7 days or less old from today's date

Posted 16 April 2012 - 06:33 AM

View PostAtli, on 16 April 2012 - 09:31 AM, said:

View PostDarenR, on 16 April 2012 - 01:23 PM, said:

you can also use the getdate() to get current date in sql

That function doesn't exist in MySQL. It's used in MSSQL and Oracle, if I am not mistaken. The equivelent for MySQL would be NOW()



Thanks for the correction on a side note the topic and forum is half cut off for me is it for you? I thought it just said sql.
Was This Post Helpful? 0
  • +
  • -

#6 Atli  Icon User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4240
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: sql query -show rows 7 days or less old from today's date

Posted 16 April 2012 - 06:41 AM

View PostDarenR, on 16 April 2012 - 01:33 PM, said:

on a side note the topic and forum is half cut off for me is it for you? I thought it just said sql.

Nope, it looks fine. The title doesn't specify which SQL server this is about, but it's posted in the MySQL forum.
Was This Post Helpful? 0
  • +
  • -

#7 Symbiot  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 28-May 10

Re: sql query -show rows 7 days or less old from today's date

Posted 16 April 2012 - 06:55 AM

thanks guys..

I am actually trying this:

SELECT * FROM jos_agora_topics WHERE posted BETWEEN UNIX_TIMESTAMP() AND UNIX_TIMESTAMP()-691200




but it is returning 0 results.. which is incorrect.. but I am guessing that I am using it incorrectly..

Perhaps you'd know how to correctly format the above?

the 'posted' column is in unix timeformat.
Was This Post Helpful? 0
  • +
  • -

#8 Atli  Icon User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4240
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: sql query -show rows 7 days or less old from today's date

Posted 16 April 2012 - 07:32 AM

View PostSymbiot, on 16 April 2012 - 01:55 PM, said:

the 'posted' column is in unix timeformat.

What do you mean by this? What type is the column?

Ideally you should store dates in one of the date formats: DATE, TIME, DATETIME or TIMESTAMP. This makes it far easier for you to manipulate the dates. If you store dates as Unix timestamps in INT columns, then you can't easily use all the date/time manipulation features in MySQL.

Quote

I am actually trying this:

You have your BETWEEN min AND max values reversed. You've got the higher value first and the lower value second. It should be the other way around.
Was This Post Helpful? 0
  • +
  • -

#9 Symbiot  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 28-May 10

Re: sql query -show rows 7 days or less old from today's date

Posted 16 April 2012 - 07:57 AM

Hi Atli.

The tables etc. are from a component for Joomla CMS and as such are premade/created etc.

There is a column in agora_posts_topics which handles dates and that column is an int with Unix as timestamp.

When I reversed the unix/unix-xxxxxx it worked! So thanks!
Was This Post Helpful? 0
  • +
  • -

#10 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 879
  • View blog
  • Posts: 1,893
  • Joined: 30-January 09

Re: sql query -show rows 7 days or less old from today's date

Posted 16 April 2012 - 07:11 PM

Question has already been answered, but like all languages, there are multiple ways of achieving the result. Here's another method:
SELECT *
FROM jos_agora_topics
WHERE posted >= UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL DAY -7))


Note that I have used the UNIX_TIMESTAMP() function here. You would do this if your field posted is of type INT/BIGINT, and stores Unix timestamps. If it is of type DATETIME, you would drop the UNIX_TIMESTAMP(), like so:
SELECT *
FROM jos_agora_topics
WHERE posted >= DATE_ADD(NOW(), INTERVAL DAY -7)



other options would include using the DATE_SUB() function that Atli mentioned, or even using DATEDIFF to compare posted and NOW().

This post has been edited by e_i_pi: 16 April 2012 - 07:15 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1