Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

Re: (OT) parsing time in mysql

by Ieronim (Friar)
on Aug 11, 2006 at 21:18 UTC ( [id://566947]=note: print w/replies, xml ) Need Help??


in reply to (OT) parsing time in mysql

I think,
SELECT * from `your_table_name` WHERE DAY(time) = DAY(NOW())
SELECT * from `your_table_name` WHERE YEAR(time) = YEAR(NOW()), DAYOFYEAR(time) = DAYOFYEAR(NOW())
etc. will work.

MySQL has corresponding WEEK, YEAR and MONTH functions, too.

I assumed that you are using MySQL 4.1.1 or above. It not, use DATE_FORMAT with corresponding formatstring instead.

UPD:I wanted to give a quick and dirty answer, but this answer was TOO dirty :) Bug is now corrected.


     s;;Just-me-not-h-Ni-m-P-Ni-lm-I-ar-O-Ni;;tr?IerONim-?HAcker ?d;print

Replies are listed 'Best First'.
Re^2: (OT) parsing time in mysql
by sulfericacid (Deacon) on Aug 12, 2006 at 00:18 UTC
    I was really looking for a more PERLish way to do this. I tried this solution and I had odd results. It brought back results from other months that occured on the same day. Today was the 11th, it showed data on the 11th over the past few months.

    Anyway around this?



    "Age is nothing more than an inaccurate number bestowed upon us at birth as just another means for others to judge and classify us"

    sulfericacid
      It brought back results from other months that occured on the same day.
      I posted wrong request :( But it can be easily extended to become right:
      -- today SELECT * from `your_table_name` WHERE YEAR(time) = YEAR(NOW()), DAYOFYEAR(time) = DAYOFYEAR(NOW()) --this month SELECT * from `your_table_name` WHERE YEAR(time) = YEAR(NOW()), MONTH(time) = MONTH(NOW()) --this week (USA) (simple solution with WEEK() --won't work at the beginning of the year SELECT * from `your_table_name` WHERE YEARWEEK(time, 0) = YEARWEEK(NOW(), 0)
      Rewriting of the request to use DATE_FORMAT can be performed according to the docs.

      I don't know if it's a Perlish way, but it's a RIGHT way. SELECT must select only the data you want, and it's faster and cleaner to perform all heavy-weight data filtering on the database side.


           s;;Just-me-not-h-Ni-m-P-Ni-lm-I-ar-O-Ni;;tr?IerONim-?HAcker ?d;print
        It almost hurts me to ask this, but what do you mean 'more Perlish'? MySQL isn't very perlish at all, unfortunately.
        However... you might try heavy use of wild-carding. That's sort of perlish.
        select * from mytable where date like curdate.'%' ;

        Or something.
        Bro. Doug The Inert.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://566947]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others perusing the Monastery: (7)
As of 2024-03-28 19:02 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found