i,
I have to write simple mysql script. I need folowing things:
Number of people logged in previous 0 to 24 hours
Number of people logged in previous 24.5 to 48 hours
Number of people logged in previous 48.5 to 72 hours
Number of people logged in previous 72.5 hours to 7 days
Number of people logged in previous 7.5 to 14 days
Number of people logged in previous 14.5 days onwards
My tables is as follows:
mysql select * from User limit 1;
+--+---+-+--+
| partner_name | user_name | last_login | mailbox_size |
+--+---+-+--+
| foo.net | edward| 2001-06-02 09:37:41 | 229099 |
+--+---+-+--+
1 row in set (0.62 sec)
I also want to save the output to comma delimited file.
Cheers
kapil
Sir, redefine the last_login column as a timestamp and try the following;
CREATE TEMPORARY TABLE time_groups
SELECT If(
Unix_timestamp(CURRENT_TIMESTAMP)
- Unix_timestamp(last_login) = 86400
, '01 day'
, If(
Unix_timestamp(CURRENT_TIMESTAMP)
- Unix_timestamp(last_login) = 172800
, '02 days'
, If(
Unix_timestamp(CURRENT_TIMESTAMP)
- Unix_timestamp(last_login) = 259200
, '03 days'
, If(
Unix_timestamp(CURRENT_TIMESTAMP)
- Unix_timestamp(last_login) = 604800
, '07 days'
, If(
Unix_timestamp(CURRENT_TIMESTAMP)
- Unix_timestamp(last_login) = 1209600
, '14 days'
, '14 days'
)
)
)
)
) AS time_group
FROM User;
SELECT time_group, Count(*)
FROM time_groups
GROUP BY time_group;
It works on my machine. The time_group values are chosen so that they
will display in the proper order.
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
MySQL list magic words: sql query database
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
