Monday, February 21, 2011

Get records from a specific month using SQLite

How would I write a SQLite query to select all records from a specific month? My dates are stored as Unix timestamps.

PHP code is allowed in your solution if it's required. SQLite2-compatible queries only, please. :)

From stackoverflow
  • If you want an efficient query then you should use BETWEEN:

    SELECT *
    FROM Table1
    WHERE date BETWEEN $start AND $end
    

    Where start and end are the unix timestamps for the start of the month and the end of the month. These can be calculated in PHP using mktime and sent as parameters.

    soren121 : Well, it's not working, but I'll have to blame my script here, because the query (and my time-finding code) seems to be correct.
    soren121 : And it was my script. Fixed the bug in MY code and it worked fine. Thanks!
  • Use:

    SELECT *
      FROM TABLE 
     WHERE DATETIME(your_unix_timestamp_col, 'unixepoch') BETWEEN YYYY-MM-DD 
                                                              AND YYYY-MM-DD
    

    Replace the YYYY-MM-DD with the dates you desire. See the reference page for other supported formats.

    Reference:

  • Without calculating last day in month

    SELECT * 
      FROM table 
     WHERE strftime('%Y-%m',date(unix_timestamp,'unixepoch','localtime')) = '2010-03'
    

How to edit the first line in a text file in c++?

I have a text file looks like this :

100 50 20 90
4.07498 0.074984
37.1704 28.1704
20.3999 14.3999
48.627 35.627 ....

I need to edit this file so that everything is kept the same except the first line, 3rd item. The ouput should look like this:

100 50 19 90
4.07498 0.074984
37.1704 28.1704
20.3999 14.3999
48.627 35.627
....

How can I do it in c++ ? Can anybody help me?

Thanks, Hoang

From stackoverflow
  • #include <stdio.h>
    
    int main()
    {
          FILE *pFile;
          pFile = fopen("example.txt", "r+");
          fseek(pFile, 7, SEEK_SET);
          fputs("19", pFile);
          fclose(pFile);
          return 0;
    }
    

    Edit: The above was of course mostly a joke. The real way to do it is to read the first line, split it into parts, change the required number, write it out, then follow with all the rest of the lines. If we know that the file contains four integers (floats?) on the first line, something like this might suffice:

    #include <fstream>
    #include <iostream>
    using namespace std;
    
    int main ()
    {
        ifstream in("in.txt");
        ofstream out("out.txt");
        float v1, v2, v3, v4;
        in >> v1 >> v2 >> v3 >> v4;
        v3 = 19.1234; // <- Do whatever you need to here.
        out << v1 << " " << v2 << " " << v3 << " " << v4;
        out << in.rdbuf();
        out.close();
        in.close();
        return 0;
    }
    
    tsubasa : Thank you for your solution. But I'm looking for a more "dynamic" way, which means I can change 20 to any number without overwriting the last number. Ex: 20, 19.9999, 19.9998, 17.36...
    Billy ONeal : @tsubasa: Then you should specify that in your question.
    calmh : Added a solution that actually fulfills the extra specifications, and can handle a new number that is longer than the previous one. :)
    tsubasa : This solution is brilliant, exactly what i'm looking for. Thank you very much calmh.
  • As long the result is the same length as the original (or shorter, and you don't mind adding spaces to cover the difference) it's pretty easy: seek to where you want to do the modification, write the new data, and you're done:

    #include <fstream>
    #include <ios>
    
    int main() { 
        std::fstream file("yourfile.txt", std::ios::in | std::ios::out);
        file.seekp(7);
        file << "19";
        return 0;
    }
    

    If the data you want to write won't "fit" in between other things you want to preserve, you need to re-write the remainder of the file, typically copying from the old file to a new one, modifying the data as required along the way through.

    Edit: something like this:

    #include <fstream>
    #include <ios>
    #include <iterator>
    #include <vector>
    
    int main() { 
        std::vector<double> data;
    
        std::fstream file("yourfile.txt", std::ios::in | std::ios::out);
        std::copy(std::istream_iterator<double>(file), 
            std::istream_iterator<double>(),
            std::back_inserter(data));
        file.clear();
        file.seekp(0);
        data[2] = 19.98;
        std::copy(data.begin(), data.end(), std::ostream_iterator<double>(file, " "));
        return 0;
    }
    

    This has a few effects that you may not want -- particularly, as it stands, it destroys whatever "line" oriented structure the original may have had, and simply writes out the result as one long line. If you want to avoid that, you can (for example) read in a line at a time, convert the numbers in the line (e.g. put then into a stringstream, then read them out of there as doubles), modify those, put the result back into a string, and write out the line with a "\n" at the end.

  • You can use a std::wfstream.

    #include <fstream>
    
    using namespace std;
    int main() {
        wfstream file(L"example.txt", std::ios::in | std::ios::out);
        std::wstring first, second, third; // The extraction operator stops on whitespace
        file >> first >> second >> third;
        third = L"Whatever I really, really, want!";
        file << first << second << third;
        file.flush(); // Commit to OS buffers or HDD right away.
    }
    

    I've probably screwed up the use of the insertion operator. But, you can reference MSDN for the exactitudes of using the wfstream. I'd absolutely advise against using any C solution, all their string manipulation functions truly suck.

Is it possible to call a procedure within an SQL statement?

Hi everyone

I thought I would use a stored routine to clean up some of my more complex SQL statements. From what I've read, it seems impossible to use a stored procedure within an sql statement, and a stored function only returns a single value when what I need is a result set. I am using mySQL v5.0

SELECT p.`id`, gi.`id`
FROM `sport`.`players` AS p JOIN `sport`.`gameinstances` AS gi
ON p.`id` = gi.`playerid`                       
WHERE (p.`playerid` IN (CALL findPlayers`("Canada", "2002")))
    AND (gi.`instanceid` NOT IN (CALL findGameInstances`("Canada", "2002")));

For example, the procedures 'findPlayers' and 'findGameInstances' are are stored routines that execute some SQL and return a result set. I would prefer not to include their code directly within the statement above.

From stackoverflow
  • Check out this discussion. This answers your questions and gives you some alternatives as well.

  • I don't know if mysql can use any of these techniques, but in SQl server I would try one of two different things (at least it might give you something to look for in th emysql documentation):

    First a table values used defined function then join to that Second, insert the results set of the sp into a temp table then join to the tem table

    You could also consider putting the complicated logic in a view and then just adding the where clause after joining to the view. This won't work if your stored proc does dynamic things a view can't do, but it is a possibilty.

Remove leading zeros from abbreviated date with PHP

Hello,

I was wondering if there is a way, using PHP, to change this date format: 01.08.86 (January 8, 1986) to this format: 1.8.86.

From stackoverflow
  • <?php
    
    $date = "01.08.86";
    $unix = strtotime($date);
    echo date('n.j.y', $unix);
    
    j-man86 : All I really needed was n.j.y Thanks alot Sean!
  • How about a regex based solution:

    $str = '01.08.86';
    $a = array('/^0(\d+)/','/\.0(\d+)/');
    $b = array('\1','.\1');
    $str = preg_replace($a,$b,$str);
    
    // $str is now '1.8.86'
    

String method to get unknown value that matches regex from string

Hi,

In Java is there a way to extract the value of an unknown subset of a string via a regex. For example in the following string "hello world 1234" I want to be able to extract the value 1234 via the regex [0-9]*. Is there a way to do this?

From stackoverflow
  • Yes - put the bit that you want into a group in the regex.

    There's a sort of tutorial here or find "capturing the string that matched a pattern" in this longer page.

  • It's actually quite easy with provided API:

    • you generate your regexp pattern by Pattern p = Pattern.compiler("regexp")
    • you try to match the pattern with the string you need: Matcher m = p.matches(string)
    • lastly you iterate over groups captured

      for (int i = 0; i < m.groupCount(); ++i) System.out.println(m.group(i));

  • Additionally, you can always check the 'official' tutorial regarding REGEX: here.

Win32 API - Create Button help

I try to create 2 buttons inside my app

case WM_CREATE:{
    hWnd =CreateWindowEx(NULL,
            L"BUTTON",
            L"Giai PTB2",
            WS_TABSTOP|WS_VISIBLE|
            WS_CHILD|BS_DEFPUSHBUTTON,
            100,
            100,
            100,
            24,
            hWnd,
            (HMENU)IDC_PTB2_BUTTON,
            hInst,
            NULL);
    HWND hWndNew =CreateWindowEx(NULL,
            L"BUTTON",
            L"Tim max",
            WS_TABSTOP|WS_VISIBLE|
            WS_CHILD|BS_DEFPUSHBUTTON,
            200,
            200,
            100,
            100,
            hWnd,
            (HMENU)IDC_PTB2_BUTTON2,
            hInst,
            NULL);
    break;
               }

The problem is , only "Giai PTB2" button shows :) Thanks first :)

From stackoverflow
  • check hWnd .. you are changing is value by the first create the side effect is that you are passing the first button as parent of the second ...

    jpyllman : And by that creating the second button inside the first button. And also hWnd is probalby your handle for the main window. So you loose that handle and doing other things wrong too.
    nXqd : Thanks it does help :)

Problem in getting refernce to ajax extender control

hi,

I am having problem in getting reference of Ajax control extender using $find() or $get or document.getElelmentById() functions in java script

Can any one tell me how can to do this?

Thanks in advance :)

From stackoverflow
  • Try this

    $get('<%=extenderId.ClientID %>') or document.getElementById('<%=extenderId.ClientID %>')

    Neo : i already tried it but it wont work for mw
    schrodinger's code : can you post your .aspx or .ascx markup here?