Hi, Everyone:
I want to do a MySQL date and time differences.
I had made a speperate table in regards to everytime a user logs in and out a datetime has been stored in the database.
This is the following MySQL script.
CREATE TABLE IF NOT EXISTS `usercounter` (
`usercounterid` varchar(35) NOT NULL DEFAULT '',
`userid` varchar(35) DEFAULT NULL,
`firstlogindate` datetime DEFAULT NULL,
`lastlogindate` datetime DEFAULT NULL,
`ipaddress` varchar(15) DEFAULT NULL,
`browseragent` varchar(150) DEFAULT NULL,
PRIMARY KEY (`usercounterid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Now I want to see the difference between the two dates and times on "lastlogindate" and "firstlogindate".
Here is the MySQL script.
SELECT usercounter.userid, usercounter.firstlogindate, usercounter.lastlogindate, usercounter.ipaddress, usercounter.browseragent, useracct.username, useracct.f_name, useracct.l_name, useracct.dateofreg,
DATEDIFF(usercounter.lastlogindate,usercounter.firstlogindate) AS differenceInDays,
TIMEDIFF(usercounter.lastlogindate,usercounter.firstlogindate) AS differenceInTime,
FROM usercounter, useracct
WHERE useracct.userid = usercounter.userid
ORDER BY usercounter.firstlogindate DESC
What I want to do is the following using "differenceInDays" and "differenceInTime" is as follows
MONTH(differenceInDays) AS m,
DAYOFMONTH(differenceInDays) AS d,
HOUR(differenceInTime) AS h,
MINUTE(differenceInTime) AS m,
SECOND(differenceInTime) AS s
However, it only works with the NOW();
MONTH(NOW()) AS m,
DAYOFMONTH(NOW()) AS d,
HOUR(NOW()) AS h,
MINUTE(NOW()) AS m,
SECOND(NOW()) AS s
I want to see my MySQL script work but it doesn't so is there a way around this?
SELECT usercounter.userid, usercounter.firstlogindate, usercounter.lastlogindate, usercounter.ipaddress, usercounter.browseragent, useracct.username, useracct.f_name, useracct.l_name, useracct.dateofreg,
DATEDIFF(usercounter.lastlogindate,usercounter.firstlogindate) AS differenceInDays, MONTH(differenceInDays) AS m, DAYOFMONTH(differenceInDays) AS d,
TIMEDIFF(usercounter.lastlogindate,usercounter.firstlogindate) AS differenceInTime, HOUR(differenceInTime) AS h, MINUTE(differenceInTime) AS m, SECOND(differenceInTime) AS s
FROM usercounter, useracct
WHERE useracct.userid = usercounter.userid
ORDER BY usercounter.firstlogindate DESC
The above gives me an error. Am I missing somethhing or is there a better way.
Thank you.
1
Reply
AdChoices