SQL Server :
     
    SELECT [activity_dt], count(*)
    FROM table1
    GROUP BY DATEPART(day, [activity_dt]), DATEPART(hour, [activity_dt]);

Oracle :

    SELECT [activity_dt], count(*)
    FROM table1
    GROUP BY TO_CHAR(activity_dt, &#39;DD&#39;), TO_CHAR(activity_dt, &#39;hh&#39;);

MySQL : 

    SELECT [activity_dt], count(*)
    FROM table1
    GROUP BY hour( activity_dt ) , day( activity_dt )


Using MySQL I usually do it that way:

    SELECT count( id ), ...
    FROM quote_data
    GROUP BY date_format( your_date_column, &#39;%Y%m%d%H&#39; )
    order by your_date_column desc;

Or in the same idea, if you need to output the date/hour: 

    SELECT count( id ) , date_format( your_date_column, &#39;%Y-%m-%d %H&#39; ) as my_date
    FROM  your_table 
    GROUP BY my_date
    order by your_date_column desc;

If you specify an index on your date column, MySQL should be able to use it to speed up things a little.

    SELECT [activity_dt], COUNT(*) as [Count]
      FROM 
     (SELECT dateadd(hh, datediff(hh, &#39;20010101&#39;, [activity_dt]), &#39;20010101&#39;) as [activity_dt]
        FROM table) abc
     GROUP BY [activity_dt]

In my case... with MySQL:

`SELECT ... GROUP BY TIMESTAMPADD(HOUR, HOUR(columName), DATE(columName))`

I have a directory full of files with one extension (.txt in this case) that I want to automatically convert to another extension (.md).

Is there an easy terminal one-liner I can use to convert all of the files in this directory to a different file extension?

Or do I need to write a script with a regular expression?



Changing file extensions for all files in a directory on OS X

I have reviewed Jasmine&#39;s documentation of the [toHaveBeenCalledWith][1] matcher in order to understand whether it&#39;s possible to pass in a regular expression for an argument, if that argument is expected to be a string. Unfortunately, this is unsupported functionality. There&#39;s also an [issue open on github][2] requesting this functionality. 

I&#39;ve dug a bit into the codebase, and I see how it might be possible to implement this inside the [existing matcher][3]. I think it would be more appropriate to implement it as a separate matcher though, so that the abstraction is captured individually.

In the meantime, what might be a good workaround?


  [1]: http://pivotal.github.com/jasmine/jsdoc/symbols/jasmine.Matchers.html#toHaveBeenCalledWith
  [2]: https://github.com/pivotal/jasmine/issues/243
  [3]: https://github.com/pivotal/jasmine/blob/master/src/core/Matchers.js#L235

Is it possible to use Jasmine&#39;s toHaveBeenCalledWith matcher with a regular expression?

I have a datetime called **activity_dt** and the data looks like this:

    2/5/2013 9:24:00 AM
    2/7/2013 7:17:00 AM

How do i group by date and hour?

DateTime group by date and hour

I have a datetime called activity_dt and the data looks like this:
<pre><code class="hljs">2/5/2013 9:24:00 AM
2/7/2013 7:17:00 AM
</code></pre>
How do i group by date and hour?

Got a big table and I want to add a column that has a randomly chosen number for each record. 1, 2, or 3.

Having a hard time. Any ideas?


MySQL UPDATE with random number between 1-3

Is it possible to do an UPDATE query in MySQL which updates field value only if certain condition is met?  Something like this:

    UPDATE test
    SET
        CASE
            WHEN true
            THEN field = 1
        END
    WHERE id = 123

In other words:

    UPDATE test
    SET
        something = 1,        /*field that always gets updated*/
        CASE
            WHEN true
            THEN field = 1    /*field that should only get updated when condition is met*/
        END
    WHERE id = 123

What is the proper way to do this?

MySQL: update a field only if condition is met

I want to set up a MySQL database for a social networking website for my college. 

My app can have at most 10,000 users.  What is the maximum number of concurrent MySQL connections possible? 


Maximum concurrent connections to MySQL

So I know in &lt;a href=&quot;http://www.mysql.com/&quot;&gt;MySQL&lt;/a&gt; it&#39;s possible to insert multiple rows in one query like so:
 
    INSERT INTO table (col1,col2) VALUES (1,2),(3,4),(5,6)

I would like to delete multiple rows in a similar way. I know it&#39;s possible to delete multiple rows based on the exact same conditions for each row, i.e.

    DELETE FROM table WHERE col1=&#39;4&#39; and col2=&#39;5&#39;
or
    
    DELETE FROM table WHERE col1 IN (1,2,3,4,5)

However, what if I wanted to delete multiple rows in one query, with each row having a set of conditions unique to itself? Something like this would be what I am looking for:

    DELETE FROM table WHERE (col1,col2) IN (1,2),(3,4),(5,6)

Does anyone know of a way to do this? Or is it not possible? 

MySQL delete multiple rows in one query conditions unique to each row

i want to read all data from one table and insert some data in to another table. my query is 
     
      INSERT INTO mt_magazine_subscription ( 
          magazine_subscription_id, 
          subscription_name, 
          magazine_id, 
          status ) 
      VALUES ( 
          (SELECT magazine_subscription_id, 
                  subscription_name, 
                  magazine_id 
           FROM tbl_magazine_subscription 
           ORDER BY magazine_subscription_id ASC), &#39;1&#39;)

but i got an error that 

      #1136 - Column count doesn&#39;t match value count at row 1

please help me.

insert data from one table to another in mysql

I have the following SQL which I need to do

    CREATE TABLE cars_users2 AS SELECT DISTINCT * FROM cars_users;
    
    DROP TABLE cars_users;
    
    ALTER TABLE cars_users2 RENAME TO cars_users;

since I cannot use heroku dataclips to drop a table, I cannot use dataclips.

So I guess I need to do this in a migration.

How do I write this sql as a migration?

How to write SQL in a migration in Rails

I need to get the total of two fields which are within the same row and input that number in a field at the end of that same row.

This is my code. 

    Sum(tbl1.fld1 + tbl1.fld2) AS [Total]

Is this what the SUM function is used for, or can you only use the SUM function for getting the total of a column?

Thanks

How to SUM two fields within an SQL query

Learning SQL. Have a simple table games with field title. I want to search based on title. If I have a game called `Age of Empires III: Dynasties`, and I use `LIKE` with parameter `Age of Empires III: Dynasties`, everything works fine, the search returns the record with that name. But if I search with `Age of Empires III`, it doesn&#39;t return any records:

    SELECT * from games WHERE (lower(title) LIKE &#39;age of empires III&#39;);


This doesn&#39;t return anything. Should I be using something else instead of `LIKE`?

I am using MySQL.

SQL like search string starts with

Consider this QUERY ([DEMO IS HERE](http://sqlfiddle.com/#!2/b7284/1))

    (SELECT * FROM `titles` where title = &#39;test\\&#39;)
    UNION ALL
    (SELECT * FROM `titles` where title LIKE &#39;test\\\\&#39;)

Output:

    | ID | TITLE |
    --------------
    |  1 | test\ |
    |  1 | test\ |


QUESTION: 
--------
Why no extra **(\\)** required for **(=)** but for (like) additional **\\\\** is required?
Its clear that MySQL escaped the **(test\\)** with **(test\\\\)** then using **(test\\\\\\\\)** is logical for LIKE.

Table information:

    CREATE TABLE IF NOT EXISTS `titles` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
    
    --
    -- Dumping data for table `titles`
    --
    
    INSERT INTO `titles` (`id`, `title`) VALUES
    (1, &#39;test\\&#39;);

How to search for slash (\) in MySQL? and why escaping (\) not required for where (=) but for Like is required?

How to split the name string in mysql ?
  
E.g.:  

    name
    -----
    Sachin ramesh tendulkar
    Rahul dravid

Split the name like `firstname,middlename,lastname`:

    firstname   middlename    lastname
    ---------  ------------   ------------
    sachin     ramesh         tendulkar
    rahul      dravid


Content Type	Original Author	Original Content on Stackoverflow
Question	user1468667	View Question on Stackoverflow
Solution 1 - Mysql	Iswanto San	View Answer on Stackoverflow
Solution 2 - Mysql	Simon	View Answer on Stackoverflow
Solution 3 - Mysql	DevT	View Answer on Stackoverflow
Solution 4 - Mysql	ggrandes	View Answer on Stackoverflow

DateTime group by date and hour

Mysql Problem Overview

Mysql Solutions

Solution 1 - Mysql

Solution 2 - Mysql

Solution 3 - Mysql

Solution 4 - Mysql

Is it possible to use Jasmine's toHaveBeenCalledWith matcher with a regular expression?

Changing file extensions for all files in a directory on OS X

Attributions