There are functions `TIMEDIFF(expr1,expr2)`, which returns the value of `expr1-expr2`, and `TIME_TO_SEC(expr3)`, which expresses `expr3` in seconds.

Note that `expr1` and `expr2` are datetime values, and `expr3` is a time value only.

Check [this link][1] for more info.


  [1]: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff

[TIMESTAMPDIFF][1] is like TIMEDIFF which Matthew states, except it returns the difference of the two datetimes in whatever units you desire (seconds, minutes, hours, etc).

For example,

    SELECT TIMESTAMPDIFF(MINUTE,LogOutTime,LogInTime) AS TimeLoggedIn
    FROM LogTable

Would return the number of minutes the user was logged in (assuming you stored this kind of thing in a table like that).


  [1]: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampdiff

I would do it like this - fetch where last activity is within 10 mins of now

    SELECT * FROM table WHERE last_activity &gt;= DATE_SUB(NOW(), INTERVAL 10 MINUTE)

```SQL
SELECT TIMESTAMPDIFF(MINUTE,LogOutTime,LogInTime) AS TimeLoggedIn
FROM LogTable
```

This example shall ruin the time if its used by using millitary time. So for calculating millitairy time I do not recommend it Because it outputs negative values.

You can try and cast them to Unix Time stamp, and take the difference.

I have an Android library that uploads data to a test server and production server. I&#39;d like developers using this library to use the test server when developing, and production server when the app is downloaded from Android Market.

Is this possible for an app to tell where it came from (Market or non-Market?) I would imagine one could detect the presence of the signed JAR file.


Detect if app was downloaded from Android Market

As a programmer I think of these as looking like &quot;the java.lang.Object at address 1a234552&quot; or similar for something like the &lt;code&gt;s&lt;/code&gt; in

    Object s = &quot;hello&quot;;

Is this correct?  Therefore are all references a fixed size?

How big is an object reference in Java and precisely what information does it contain?

I want to subtract between two date time values using SQL in MySQL such that I get the interval in minutes or seconds. Any ideas? I want to run a SQL query that retrieves uses from a database who have logged in like 10 minutes from the time.

How do I subtract using SQL in MYSQL between two date time values and retrieve the result in minutes or second?

<p>I want to subtract between two date time values using SQL in MySQL such that I get the interval in minutes or seconds. Any ideas? I want to run a SQL query that retrieves uses from a database who have logged in like 10 minutes from the time.</p>


I have googled this and keep coming up with &quot;No it is not possible&quot; but these posts were dated 2005-2007 so I&#39;m wondering if this has been changed. A code example:

    CREATE PROCEDURE `blah`
    (
      myDefaultParam int = 0 -- This breaks the code for some reason
    )
    BEGIN
      -- Do something here
    END

One of the solutions has been to pass null and then check for null and set the variable. I don&#39;t want to do that and I shouldn&#39;t have to. If this is true then MySql devs need to wake up because there is so much more I could do with MSSQL.

Is it possible to have a default parameter for a mysql stored procedure?

I am trying to generate a random integer for each row I select between 1 and 60 as timer.

    SELECT downloads.date, products.*, (FLOOR(1 + RAND() * 60)) AS timer

I have searched and keep coming up to this FLOOR function as how to select a random integer in a range. This is giving me a 1 for every row.
What am I missing?

I am on mysql 	5.0.75

Heres the rest of the query I belive it might be a nesting issue

    SELECT *
    FROM (
     SELECT downloads.date, products.*, FLOOR(1 + (RAND() * 60)) AS randomtimer, 
     (
     SELECT COUNT( * )
     FROM distros
     WHERE distros.product_id = products.product_id
     ) AS distro_count,
    
     (SELECT COUNT(*) FROM downloads WHERE downloads.product_id = products.product_id) AS true_downloads
    
     FROM downloads
     INNER JOIN products ON downloads.product_id = downloads.product_id
    ) AS count_table
    WHERE count_table.distro_count &gt; 0
    AND count_table.active = 1
    ORDER BY count_table.randomtimer , count_table.date DESC LIMIT 10

How to get mysql random integer range?

Is there any SQL subquery syntax that lets you define, literally, a temporary table?

For example, something like

    SELECT
      MAX(count) AS max,
      COUNT(*) AS count
    FROM
      (
        (1 AS id, 7 AS count),
        (2, 6),
        (3, 13),
        (4, 12),
        (5, 9)
      ) AS mytable
      INNER JOIN someothertable ON someothertable.id=mytable.id

This would save having to do two or three queries: creating temporary table, putting data in it, then using it in a join.

I am using MySQL but would be interested in other databases that could do something like that.

Can you define &quot;literal&quot; tables in SQL?

I have a table with ~500k rows; varchar(255) UTF8 column `filename` contains a file name;

I&#39;m trying to strip out various strange characters out of the filename - thought I&#39;d use a character class: `[^a-zA-Z0-9()_ .\-]`

Now, **is there a function in MySQL that lets you replace through a regular expression**? I&#39;m looking for a similar functionality to REPLACE() function - simplified example follows:
    
    SELECT REPLACE(&#39;stackowerflow&#39;, &#39;ower&#39;, &#39;over&#39;);
    
    Output: &quot;stackoverflow&quot;

    /* does something like this exist? */
    SELECT X_REG_REPLACE(&#39;Stackoverflow&#39;,&#39;/[A-Zf]/&#39;,&#39;-&#39;); 

    Output: &quot;-tackover-low&quot;

I know about [REGEXP/RLIKE][1], but those only check *if* there is a match, not *what* the match is.

(I *could* do a &quot;`SELECT pkey_id,filename FROM foo WHERE filename RLIKE &#39;[^a-zA-Z0-9()_ .\-]&#39;`&quot; from a PHP script, do a `preg_replace` and then &quot;`UPDATE foo ... WHERE pkey_id=...`&quot;, but that looks like a last-resort slow &amp; ugly hack)


  [1]: https://stackoverflow.com/a/6943142/19746

How to do a regular expression replace in MySQL?

I upgraded to Snow Leopard using the disc we got at WWDC.

Trying to run some of my rails apps now complains about sql

        (in /Users/coneybeare/Projects/Ambiance/ambiance-server)
    !!! The bundled mysql.rb driver has been removed from Rails 2.2. Please install the mysql gem and try again: gem install mysql.
    Importing all sounds in /Users/coneybeare/Projects/Ambiance/ambiance-sounds/Import 32/Compressed/
    
     -- AdirondackPeepers.caf
    !!! The bundled mysql.rb driver has been removed from Rails 2.2. Please install the mysql gem and try again: gem install mysql.
    rake aborted!
    dlopen(/opt/local/lib/ruby/gems/1.8/gems/mysql-2.7/lib/mysql.bundle, 9): Library not loaded: /usr/local/mysql/lib/libmysqlclient.16.dylib
      Referenced from: /opt/local/lib/ruby/gems/1.8/gems/mysql-2.7/lib/mysql.bundle
      Reason: image not found - /opt/local/lib/ruby/gems/1.8/gems/mysql-2.7/lib/mysql.bundle
    
    (See full trace by running task with --trace)



I could have sworn I fixed this once before.  The problem is that

    sudo gem install mysql

does not work and gives the error:

   

     Building native extensions.  This could take a while...
    ERROR:  Error installing mysql:
    	ERROR: Failed to build gem native extension.
    
    /opt/local/bin/ruby extconf.rb install mysql
    checking for mysql_query() in -lmysqlclient... no
    checking for main() in -lm... yes
    checking for mysql_query() in -lmysqlclient... no
    checking for main() in -lz... yes
    checking for mysql_query() in -lmysqlclient... no
    checking for main() in -lsocket... no
    checking for mysql_query() in -lmysqlclient... no
    checking for main() in -lnsl... no
    checking for mysql_query() in -lmysqlclient... no
    
    
    Gem files will remain installed in /opt/local/lib/ruby/gems/1.8/gems/mysql-2.7 for inspection.
    Results logged to /opt/local/lib/ruby/gems/1.8/gems/mysql-2.7/gem_make.out

Has anybody gotten mysql to work with rails on snow leopard yet?  If so, what is your setup and better yet, what can I do to reproduce it?




Rails, MySQL and Snow Leopard

In SQL Server 2005, is there a concept of a one-time-use, or local function declared inside of a SQL script or Stored Procedure? I&#39;d like to abstract away some complexity in a script I&#39;m writing, but it would require being able to declare a function.

Just curious.

Can I create a One-Time-Use Function in a Script or Stored Procedure?

In SQL, how can I remove the first 4 characters of values of a specific column in a table?  Column name is `Student Code` and an example value is `ABCD123Stu1231`.
I want to remove first 4 chars from my table for all records

Please guide me

How do I remove the first characters of a specific column in a table?

I have to update a field with a value which is returned by a join of 3 tables.

Example: 

    select
        im.itemid
        ,im.sku as iSku
        ,gm.SKU as GSKU
        ,mm.ManufacturerId as ManuId
        ,mm.ManufacturerName
        ,im.mf_item_number
        ,mm.ManufacturerID
    from 
        item_master im, group_master gm, Manufacturer_Master mm 
    where
        im.mf_item_number like &#39;STA%&#39;
        and im.sku=gm.sku
        and gm.ManufacturerID = mm.ManufacturerID
        and gm.manufacturerID=34

I want to update the `mf_item_number` field values of table `item_master` with some other value which is joined in the above condition.

How can I do this in MS SQL Server?


SQL update query using joins

How do I get `datetime.datetime.now()` printed out in the native language?


        &gt;&gt;&gt; session.deathDate.strftime(&quot;%a, %d %b %Y&quot;)
        &#39;Fri, 12 Jun 2009&#39;

I&#39;d like to get the same result but in local language.

Locale date formatting in Python

I want to create a list of dates, starting with today, and going back an arbitrary number of days, say, in my example 100 days. Is there a better way to do it than this?

    import datetime
    
    a = datetime.datetime.today()
    numdays = 100
    dateList = []
    for x in range (0, numdays):
        dateList.append(a - datetime.timedelta(days = x))
    print dateList




Creating a range of dates in Python

Is there a cross-platform way to get the current date and time in C++?

How to get current time and date in C++?

I am trying to parse this date with `SimpleDateFormat` and it is not working:

    import java.text.ParseException;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    
    public class Formaterclass {
        public static void main(String[] args) throws ParseException{
            String strDate = &quot;Thu Jun 18 20:56:02 EDT 2009&quot;;
            SimpleDateFormat formatter = new SimpleDateFormat(&quot;yyyy-MM-dd&quot;);
            Date dateStr = formatter.parse(strDate);
            String formattedDate = formatter.format(dateStr);
            System.out.println(&quot;yyyy-MM-dd date is ==&gt;&quot;+formattedDate);
            Date date1 = formatter.parse(formattedDate);
    
            formatter = new SimpleDateFormat(&quot;dd-MMM-yyyy&quot;);
            formattedDate = formatter.format(date1);
            System.out.println(&quot;dd-MMM-yyyy date is ==&gt;&quot;+formattedDate);
        }
    }

If I try this code with strDate=`&quot;2008-10-14&quot;`, I have a positive answer. What&#39;s the problem? How can I parse this format?

PS. I got this date from a `jDatePicker` and there is no instruction on how modify the date format I get when the user chooses a date.


How to parse a date?

I&#39;m writing an equipment rental application where clients are charged a fee for renting equipment based on the duration (in days) of the rental. So, basically, (daily fee * number of days) = total charge.

For instant feedback on the client side, I&#39;m trying to use Javascript to figure out the difference in two calendar dates. I&#39;ve searched around, but nothing I&#39;ve found is quite what I&#39;m looking for. Most solutions I&#39;ve seen are of the form:

    function dateDiff1(startDate, endDate) {
        return ((endDate.getTime() - startDate.getTime()) / 1000*60*60*24);
    }

My problem is that equipment can be checked out and returned at &lt;i&gt;any time of day&lt;/i&gt; during those two dates with no additional charge. The above code is calculating the number of 24 hour periods between the two dates, when I&#39;m really interested in the number of calendar days.

For example, if someone checked out equipment at 6am on July 6th and returned it at 10pm on July 7th, the above code would calculate that more than one 24 hour period had passed, and would return 2. The desired result is 1, since only one calendar date has elapsed (i.e. the 6th to the 7th).

The closest solution I&#39;ve found is this function:

    function dateDiff2(startDate, endDate) {
        return endDate.getDate() - startDate.getDate();
    }

which does exactly what I want, as long as the two dates are within the same month. However, since getDate() only returns the day of month (i.e. 1-31), it doesn&#39;t work when the dates span multiple months (e.g. July 31 to August 1 is 1 day, but the above calcuates 1 - 31, or -29).

On the backend, in PHP, I&#39;m using gregoriantojd(), which seems to work just fine (see [this post](http://no.php.net/manual/en/function.date.php#84942) for an example). I just can&#39;t find an equivalent solution in Javascript.

Anyone have any ideas?

Content Type	Original Author	Original Content on Stackoverflow
Question	Ali	View Question on Stackoverflow
Solution 1 - Mysql	Matthew Jones	View Answer on Stackoverflow
Solution 2 - Mysql	Welbog	View Answer on Stackoverflow
Solution 3 - Mysql	David Snabel-Caunt	View Answer on Stackoverflow
Solution 4 - Mysql	Jasper Lankhorst	View Answer on Stackoverflow
Solution 5 - Mysql	Andrew Whittington	View Answer on Stackoverflow

How do I subtract using SQL in MYSQL between two date time values and retrieve the result in minutes or second?

Mysql Problem Overview

Mysql Solutions

Solution 1 - Mysql

Solution 2 - Mysql

Solution 3 - Mysql

Solution 4 - Mysql

Solution 5 - Mysql

How big is an object reference in Java and precisely what information does it contain?

Detect if app was downloaded from Android Market

Attributions