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'

In reply to (OT) parsing time in mysql by sulfericacid

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.