SELECT id,from_unixtime(pubdate) FROM sq_question WHERE TO_DAYS( NOW( ) ) - TO_DAYS(from_unixtime(pubdate)) IN (1,2,4,7,15,30);
今天
select * from 表名 where to_days(时间字段名) = to_days(now());
昨天
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1
近7天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)
近30天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)
本月
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
上一月
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1
查询本季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());
查询上季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
查询本年数据
select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());
查询上年数据
select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));
查询当前这周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());
查询上周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;
查询上个月的数据
select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m') select * from user where DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ; select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now()) select * from user where MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) select * from user where YEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = YEAR(now()) and MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) select * from user where pudate between 上月最后一天 and 下月第一天
查询当前月份的数据
select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')
查询距离当前现在6个月的数据
select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();
<!DOCTYPE html>
<html>
<head lang="en">
<meta charset="UTF-8">
<title>主播录制界面</title>
</head>
<body>
<video id="video" autoplay="" style='width:640px;height:480px'></video>
<canvas id="output" style="display:none"></canvas>
<script type="text/javascript" charset="utf-8">
var ws = new WebSocket("ws://自己服务器ip:8888");
var back = document.getElementById('output');
var backcontext = back.getContext('2d');
var video = document.getElementById("video");
var success = function(stream){
video.src = window.URL.createObjectURL(stream);
}
ws.onopen = function(){
draw();
}
var draw = function(){
try{
backcontext.drawImage(video,0,0, back.width, back.height);
}catch(e){
if (e.name == "NS_ERROR_NOT_AVAILABLE") {
return setTimeout(draw, 100);
} else {
throw e;
}
}
if(video.src){
ws.send(back.toDataURL("image/jpeg", 0.5));
}
setTimeout(draw, 100);
}
navigator.getUserMedia = navigator.getUserMedia || navigator.webkitGetUserMedia ||
navigator.mozGetUserMedia || navigator.msGetUserMedia;
navigator.getUserMedia({video:true, audio:false}, success, console.log);
</script>
</body>
</html>
4.function.php 统计数据页面
<?php
//统计在线人数
function clearDir($dir)
{
$n = 0;
if ($dh = opendir($dir))
{
while (($file = readdir($dh)) !== false)
{
if ($file == '.' or $file == '..')
{
continue;
}
if (is_file($dir . $file)) {
$n++;
}
}
}
closedir($dh);
return $n;
}
//通知在线的人
function notice($dir){
if ($dh = opendir($dir))
{
while (($file = readdir($dh)) !== false)
{
if ($file == '.' or $file == '..')
{
continue;
}
if (is_file($dir . $file)) {
$array[]=file_get_contents($dir.$file);
}
}
}
closedir($dh);
return $array;
}
5.在同级目录下建立client文件，存放信息
PHP 即使通讯
1.socket.php 一样，使用时需要开启
<?php
//创建websocket服务器对象，监听0.0.0.0:9502端口
$ws = new swoole_websocket_server("0.0.0.0", 9502);
//监听WebSocket连接打开事件
$ws->on('open', function ($ws, $request) {
$fd[] = $request->fd;
$GLOBALS['fd'][] = $fd;
//$ws->push($request->fd, "hello, welcome\n");
});
//监听WebSocket消息事件
$ws->on('message', function ($ws, $frame) {
$msg = 'from'.$frame->fd.":{$frame->data}\n";
//var_dump($GLOBALS['fd']);
//exit;
foreach($GLOBALS['fd'] as $aa){
foreach($aa as $i){
$ws->push($i,$msg);
}
}
// $ws->push($frame->fd, "server: {$frame->data}");
// $ws->push($frame->fd, "server: {$frame->data}");
});
//监听WebSocket连接关闭事件
$ws->on('close', function ($ws, $fd) {
echo "client-{$fd} is closed\n";
});
$ws->start();
2.socket.html聊天页面
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<div id="msg"></div>
<input type="text" id="text">
<input type="submit" value="发送数据" onclick="song()">
</body>
<script>
var msg = document.getElementById("msg");
var wsServer = 'ws://60.205.208.176:9502';
//调用websocket对象建立连接：
//参数：ws/wss(加密)：//ip:port （字符串）
var websocket = new WebSocket(wsServer);
//onopen监听连接打开
websocket.onopen = function (evt) {
//websocket.readyState 属性：
/*
CONNECTING 0 The connection is not yet open.
OPEN 1 The connection is open and ready to communicate.
CLOSING 2 The connection is in the process of closing.
CLOSED 3 The connection is closed or couldn't be opened.
*/
msg.innerHTML = websocket.readyState;
};
function song(){
var text = document.getElementById('text').value;
document.getElementById('text').value = '';
//向服务器发送数据
websocket.send(text);
}
//监听连接关闭
// websocket.onclose = function (evt) {
// console.log("Disconnected");
// };
//onmessage 监听服务器数据推送
websocket.onmessage = function (evt) {
msg.innerHTML += evt.data +'<br>';
// console.log('Retrieved data from server: ' + evt.data);
};
//监听连接错误信息
// websocket.onerror = function (evt, e) {
// console.log('Error occured: ' + evt.data);
// };
</script>
</html>
<?php
namespace console\controllers;
use yii\console\Controller;
class TestController extends Controller {
public function actionIndex()
{
echo "hello world";
}
}
