Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2011

    Unanswered: MYSQL Query 4 Last month data

    Hello guys,
    I think I need a little more help ...
    For the query below, I need to get data from last month. Right now, everytime I have to change the query (the dates at the bottom), and then run it to get the right results. I just came across this thread, and it seems like it can be of great help for me. However, I am not able to make it work with the two solutions above. All I want is first date of previous month as the first evttime, and last day of previous month as the higher limit. I know there is a way in MS SQL, getdate(), but it does not work in mysql... and ...

    I will appreciate if anyone can help me with that.

    select s.sic as "SIC Code", s.cat_name as "SIC Name", count(*) as 'Requests'
    from clientservices.p417_caller_revenue_summary r, pmartin.category_names s
    where r.evtcode = 4
    and r.evtnumber = s.sic
    and evttime between unix_timestamp(20110201)
    and unix_timestamp(20110301)
    group by s.sic

    Thanks again.


  2. #2
    Join Date
    Sep 2009
    San Sebastian, Spain
    What is the datatype for evttime? The UNIX_TIMESTAMP returns an unsigned integer of the number of seconds since 01 Jan 1970. However, its parameter must be a valid date, datetime or timestamp value. Look at STR_TO_DATE function which will allow you to format your date correctly.

    So UNIX_TIMESTAMP(STR_TO_DATE('20110201','%Y%d%m')) is most likely what you are looking for.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts