I need help with a query to get the amount of minutes from DATETIME
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:
SELECT (FORMULA TO GET ELAPSED TIME [HIGHEST VAL - LOWEST VAL])
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
You should be able to use the ORDER BY clause to get them the way you want.
So are you saying that I should use sub-selects to put out the values together into 1 query to do the math?
I misread your question at first, but that would be one way of doing it.
I was able to get it to work with this query:
(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
WHERE ipaddress = "10.10.1.100" LIMIT 1;
i doubt that it's the most optimized way of doing it but it works..
Wrote this some time ago - makes actually the same - (But looks quite messy...)
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....
|All times are GMT. The time now is 07:45 AM.|
Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.1.0