>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
