Where > 2002 < 2003

[miles_rich]

what is the syntax so that i can pull out records between years 2002 and 2003 from a unix timestamp?

[cahva]

SELECT * FROM table WHERE datefield > UNIX_TIMESTAMP('2002-1-1 00:00:00') AND datefield < UNIX_TIMESTAMP('2003-12-31 23:59:59')

That would pull out records from the years 2002 and 2003.

[miles_rich]

select id, cust_id, count(price) as num_of_quotes, sum(price) as total from tbl_quote where quote_type = 'quote_accepted' and inf_stamp > UNIX_TIMESTAMP('2003-01-01 00:00:00') and inf_stamp < UNIX_TIMESTAMP('2003-12-12 24:59:59') group by cust_id order by total desc limit 25;

so is this correct?

[cahva]

Almost good. Change UNIX_TIMESTAMP('2003-12-12 24:59:59') to UNIX_TIMESTAMP('2003-12-12 23:59:59').

I think theres no such time as 24:59:59... 🙂 Or do we have 25 hour days now? 😃

Hmm.. It would also be good to put the inf_stamps inside ( ). Heres the query:

select id, cust_id, count(price) as num_of_quotes, sum(price) as total from tbl_quote 
where quote_type = 'quote_accepted' 
and (inf_stamp > UNIX_TIMESTAMP('2003-01-01 00:00:00') 
and inf_stamp < UNIX_TIMESTAMP('2003-12-12 23:59:59')) 
group by cust_id 
order by total desc limit 25;

[miles_rich]

this doesnt work, all i get is "empty record set"
sorry about the time format been working for so long i have no concept of time.😃

[jonsof]

What if we would like to pull out the records between 3-04-2003 and 16-07-2003(assuming there are date fields)

[cahva]

@:
If the fields type is DATE, then all we have to do is change it to unix timestamp when fetching rows.
Pulling out with datefields is done like this (YYYY-MM-DD):

SELECT * FROM table WHERE UNIX_TIMESTAMP(datefield) >= UNIX_TIMESTAMP('2003-04-03') AND UNIX_TIMESTAMP(datefield) <=UNIX_TIMESTAMP('2003-07-16')

@:
I saw a little bug in my previous query:
and (inf_stamp > UNIX_TIMESTAMP('2003-01-01 00:00:00')
and inf_stamp < UNIX_TIMESTAMP('2003-12-12 23:59:59'))

Should be:
and (inf_stamp >= UNIX_TIMESTAMP('2002-01-01 00:00:00')
and inf_stamp <= UNIX_TIMESTAMP('2003-12-31 23:59:59'))

If you cant get it to work,try first to fetch the rows with no GROUP BY and WHERE quote_type=...:

select id, cust_id, count(price) as num_of_quotes, sum(price) as total from tbl_quote 
where inf_stamp >= UNIX_TIMESTAMP('2002-01-01 00:00:00') 
and inf_stamp <= UNIX_TIMESTAMP('2003-12-31 23:59:59')
order by total desc limit 25;

Do you get any rows with that?

[jonsof]

Is this correct?

SELECT * FROM A_TABLE WHERE now() BETWEEN 2003-04-3 AND 2003-07-16

???

[cahva]

I dont think that would work. First of all, use simple quotes(') around the dates. Otherwise the between doesnt work.

I dont know why have you put NOW() in the query? You should put the date-field you are using instead of NOW() to pull the records out.

SELECT * FROM A_TABLE WHERE datefield BETWEEN '2003-04-3' AND '2003-07-16'

[jonsof]

i have two datefields, 1st named date_posted and the 2nd is named date_expired so i want to pull out the rows which meet this requirement "something like showing thread" automatic queries.

So i re-ask if this syntax is correct.