I need help with a query to get the amount of minutes from DATETIME

webosb · 04-09-2009, 03:34 PM

So I have a column called "signupdate" that is a datetime datatype. And I want to be able to get the elapsed time it took between signups of a user with the same ip address. I never formulated datetime datatypes before, can anyone offer any assistance?

say if i had rows of data as so:

ipaddress|signupdate
10.10.1.100|2009-04-08 22:20:01
10.10.1.100|2009-04-08 22:23:12
10.10.1.100|2009-04-08 22:24:30
10.10.1.100|2009-04-08 22:25:32
10.10.1.100|2009-04-08 22:26:16

SELECT (FORMULA TO GET ELAPSED TIME [HIGHEST VAL - LOWEST VAL])
FROM users
WHERE ipaddress = "10.10.1.100"

Basically to do this and output 6 minutes:
22:26:16 - 22:20:01 = 6:15

I'm getting stuck on how to get the highest value and lowest value from the 5 rows of data
I

Village Idiot · 04-09-2009, 03:50 PM

You should be able to use the ORDER BY clause to get them the way you want.

webosb · 04-09-2009, 03:55 PM

So are you saying that I should use sub-selects to put out the values together into 1 query to do the math?

Village Idiot · 04-09-2009, 04:12 PM

I misread your question at first, but that would be one way of doing it.

webosb · 04-09-2009, 05:25 PM

I was able to get it to work with this query:

SELECT
round(
(
(SELECT unix_timestamp(signupdate) FROM users WHERE ipaddress = "10.10.1.100" ORDER BY signupdate DESC LIMIT 1)
-
(SELECT unix_timestamp(signupdate) FROM users WHERE ipaddress = "10.10.1.100" ORDER BY signupdate ASC LIMIT 1)
)/60) as elapsed
FROM users
WHERE ipaddress = "10.10.1.100" LIMIT 1;

i doubt that it's the most optimized way of doing it but it works..

Sakakuchi · 04-09-2009, 07:51 PM

Wrote this some time ago - makes actually the same - (But looks quite messy...)

PHP Code:


			
    public function protectBruteforce()

  {

        $ip = $this->getUserIP();

        $core = new core;

        $query = sprintf("SELECT * FROM log_failed_login WHERE IP = '%s' AND `time` > '%s' LIMIT 0,7",

        $core->cleanSQL($ip), time() - 600);

        $result = mysql_query($query) or die('Could not perform query.');

        if(mysql_num_rows($result) > 6)

    {

            die('loginLimitExceeded');

        }

        mysql_free_result($result);

    }

maZtah · 04-10-2009, 11:46 AM

I would also store the date/time as a unix timestamp (integer).

allworknoplay · 04-10-2009, 11:51 AM

Quote:

Originally Posted by maZtah

I would also store the date/time as a unix timestamp (integer).

That's how I store my dates, it's easier for me to manipulate and do calculations this way....