Pulling weekdays

nolph · #1 (**permalink**) 01-23-12, 12:11

Hello,
ive ran into a snag with my sql code and need a hand if possible.

Code:

Select distinct from_unixtime(timestamp, '%Y-%m-%d') from netagent.os_table 
where from_unixtime(timestamp, '%Y-%m-%d') > CURDATE() - INTERVAL 40 DAY 
order by from_unixtime(timestamp, '%Y-%m-%d')

right now it pulls the dates specified. What i need to do is exclude the weekends. How would i do that?

If anyone could help that would be great. Thanks

it-iss.com · #2 (**permalink**) 01-23-12, 13:15

Using the DATE_FORMAT function it can return the Day of the week %a. Using this we can have:

Select distinct from_unixtime(timestamp, '%Y-%m-%d') from netagent.os_table
where from_unixtime(timestamp, '%Y-%m-%d') > CURDATE() - INTERVAL 40 DAY
AND DATE_FORMAT(timestamp, '%a') NOT IN ('Sat','Sun')
order by from_unixtime(timestamp, '%Y-%m-%d')

r937 · #3 (**permalink**) 01-23-12, 14:49

instead of DATE_FORMAT, i believe you want FROM_UNIXTIME

nolph · #4 (**permalink**) 01-23-12, 14:53

Tried that code and nothing was outputted to the window... There are no words in the dates stored in the DB. its all numbers.

nolph · #5 (**permalink**) 01-23-12, 14:59

Quote:

Originally Posted by r937

instead of DATE_FORMAT, i believe you want FROM_UNIXTIME

What would i have to change in the code? im not sure what to do.

r937 · #6 (**permalink**) 01-23-12, 15:33

change this --

Code:

AND DATE_FORMAT(timestamp, '%a') NOT IN ('Sat','Sun')

to this --

Code:

AND FROM_UNIXTIME(timestamp, '%a') NOT IN ('Sat','Sun')

nolph · #7 (**permalink**) 01-23-12, 17:09

Quote:

Originally Posted by r937

change this --

Code:

AND DATE_FORMAT(timestamp, '%a') NOT IN ('Sat','Sun')

to this --

Code:

AND FROM_UNIXTIME(timestamp, '%a') NOT IN ('Sat','Sun')

That worked like a charm. Thank you

Internet Services

Web Development

Digital Marketing

Consumer Tech