Summary of typical mysql date functions

Mysql common date functions: 1. UNIX_TIMESTAMP ()? This function returns the number of seconds from 1970 to the present. The parameter can also be included in the brackets, that is, the number of seconds from January 1, 1970 to the specified time period. Note that this is the number of seconds, because the System. currentTimeMillis ()

Mysql common date functions: 1. UNIX_TIMESTAMP ()? This function returns the number of seconds from 1970 to the present. The parameter can also be included in the brackets, that is, the number of seconds from January 1, 1970 to the specified time period. Note that this is the number of seconds, because the System. currentTimeMillis ()

Summary of commonly used mysql date functions

Mysql functions that are frequently used in recent statistics:

1. UNIX_TIMESTAMP ()?

This function returns the number of seconds from 1970 to the present. The parameter can also be included in the brackets, that is, the number of seconds from January 1, 1970 to the specified time period. Note that this is the number of seconds, because the System. currentTimeMillis () returns the number of milliseconds

?

2. date_format ()

For example, date_format (now (), '% Y-% m-% d % H: % I: % s') converts the current time to a string in a certain time format, of course, the default format of the now () function is '% Y-% m-% d % H: % I: % s'

?

3. FROM_UNIXTIME ()

FROM_UNIXTIME (1390357150, '% Y-% m-% D'); converts the number of seconds from January 1, 1970 to the specified time range to a fixed time format,

This applies to the number of seconds for storing the background date. The foreground display requires an application of a specific date.

?

4. current_date ()

This function returns the current date, but this function has a Bug in date subtraction,

Take today as an example:

Select current_date ();

2014-01-24

I want to know the date of the last 30 days

Select current_date ()-30;

20140094

Obviously it is not the expected value. After testing, it is found that an error will occur when the return carry of the month or year is involved.

For the thirty days before today, you can do this in milliseconds.

Select FROM_UNIXTIME (UNIX_TIMESTAMP (current_date ()-24*60*60*30, '% Y-% m-% D ');

Which of the following is a better method?

?

With the above functions, you can simply make statistics based on the online quantity,

Related table

? User_online_count_tbl

Related Fields

? User_online_num (current number of online users)

? Online_statistic_time (the time when the number of online messages is counted)

?

?

5. Statistics on the average number of online users in the last 10 days

select   sum(user_online_num)/count(online_statistic_time) as "avg_online_num",  FROM_UNIXTIME(online_statistic_time,'%Y-%m-%d') as "dayTime"from  user_online_count_tblwhere  FROM_UNIXTIME(online_statistic_time,'%Y-%m-%d') <= date_format(current_date(),'%Y-%m-%d')and   FROM_UNIXTIME(online_statistic_time,'%Y-%m-%d') >= FROM_UNIXTIME(UNIX_TIMESTAMP(current_date())-24*60*60*10,'%Y-%m-%d')group by   FROM_UNIXTIME(online_statistic_time,'%Y-%m-%d')

?

?