Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

(OT) parsing time in mysql

by sulfericacid (Deacon)
on Aug 11, 2006 at 21:08 UTC ( #566946=perlquestion: print w/ replies, xml ) Need Help??
sulfericacid has asked for the wisdom of the Perl Monks concerning the following question:

This was partially discussed in the CB earlier this morning but thought the idea would be easier if there were some examples.

I have a column "time" with timestamps that look like 2006-08-10 10:48:21. I need to select them based on TODAY, THIS WEEK, THIS MONTH, etc.

I am currently using CURDATE(),INTERVAL $by ($by: 1 day, 7 day, etc) which I could probably get by with but it's not quite right. I don't want a "past 24 hours" select, I want a select from today since midnight. On the same token, I don't want the past 7 days, I want this week from Sunday. Same goes for the current month and year.

Selecting an interval on 1 year or even 365 days brings back a year from the current date, not the year of 2006.

I'm looking for non-modular solutions on how this could be pieced together.



"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

2006-08-11 Retitled by planetscape, as per Monastery guidelines

( keep:0 edit:7 reap:2 )

Original title: 'parsing time in mysql'

Comment on (OT) parsing time in mysql
Re: (OT) parsing time in mysql
by Ieronim (Friar) on Aug 11, 2006 at 21:18 UTC
    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
      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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://566946]
Approved by Ieronim
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (4)
As of 2014-07-10 03:12 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (198 votes), past polls