query by date

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jamrop
Forum Commoner
Posts: 80
Joined: Fri May 16, 2003 5:38 pm

query by date

Post by jamrop »

I wonder if it is possible to have a query that uses the date

e.g

table has a tv series list. lets say therre is a tv_id(primary key), tv_name, tv_episode, date

in php u query to show the whole list - select * from tvseries.
and it is displyed in a table.

what i want to know, if u update the epsiode so u had eps 1-10, but now u have 1-15, is it possible to highlight tv series that have been updated within 2 weeks?

Many thanks
Gleeb
Forum Commoner
Posts: 87
Joined: Tue May 13, 2003 7:01 am
Location: UK
Contact:

Post by Gleeb »

Sure, it's possible.

Code: Select all

SELECT * FROM table WHERE date = now()-60*60*24*14
60 secondind in 60 minutes in 24 hours in 14 days. 1209600 seconds in total. that should work :)

Just as a quick note, try the databases forum for SQL stuff, it'll help you get a reply faster.
User avatar
Friday
Forum Newbie
Posts: 5
Joined: Wed May 21, 2003 4:16 am
Location: China

Post by Friday »

Code: Select all

select * from TABLE_NAME where date=date_add(now(),interval -14 day)
jamrop
Forum Commoner
Posts: 80
Joined: Fri May 16, 2003 5:38 pm

Post by jamrop »

thanks for reply, but if u have already got e.g select * from tvseries, which displays all the listing, then u need another query (date query) to put in, so it can highlight the updates

how would u get around that?
Gleeb
Forum Commoner
Posts: 87
Joined: Tue May 13, 2003 7:01 am
Location: UK
Contact:

Post by Gleeb »

A quick correction to my SQL. It has the wrong operand. A cookie to the one who finds it ;)

You don't need a second Query to find that out, PHP is quite capable of handling dates :)
jamrop
Forum Commoner
Posts: 80
Joined: Fri May 16, 2003 5:38 pm

Post by jamrop »

hey

i have tried both those codes in my php, but it does not seem to find anything. IS the sql code just looking for anything that was updated 2 weeks ago, Or within 2 weeks?



Many thanks
nincha
Forum Contributor
Posts: 191
Joined: Fri Mar 28, 2003 12:30 pm
Location: CA, USA

Post by nincha »

:lol: 8O :lol: 8O
jamrop
Forum Commoner
Posts: 80
Joined: Fri May 16, 2003 5:38 pm

Post by jamrop »

?????????????????????

Please its doing my head in
User avatar
coolpravin
Forum Newbie
Posts: 20
Joined: Mon May 19, 2003 12:56 am
Location: India

solution..

Post by coolpravin »

use this one

Code: Select all

SELECT * FROM tvseries WHERE TO_DAYS( NOW( ) ) - TO_DAYS(date ) <= 7
Here date is supposed to be name of the colum .

Warning :-
1)Do not use date as column name instead you can use ondate.
2)Always store date in Y-m-d format to database.
ckuipers
Forum Commoner
Posts: 61
Joined: Mon Mar 24, 2003 6:10 am

Re: solution..

Post by ckuipers »

coolpravin wrote:use this one

Code: Select all

SELECT * FROM tvseries WHERE TO_DAYS( NOW( ) ) - TO_DAYS(date ) <= 7
Here date is supposed to be name of the colum .

Warning :-
1)Do not use date as column name instead you can use ondate.
2)Always store date in Y-m-d format to database.
I always use the unix timestamp as a date in the database. Takes some formatting but it's very easy to work with when you have to calculate with dates.
Post Reply