唠叨帮忙喔 php mysql的日期问题

[phpeye]

表中有一个字段是date1 是用来放秒的

如何得到 今日 本周 本月 这三个的访问量?

谢谢,在线等你

[zeroleonhart]

活用php里date和strtotime函数

[phpeye]

zeroleonhart,给点例子看看呢，好吗

[bflovesnow]

为啥不看看手册呢？？

[phpeye]

本日=08
本月=03
本周=Thursday

这样真的可以吗?

[天降大任于斯]

对本日最保险最原始的方法:

$year = date("y"); //本年
$month = date("n"); //本月
$day = date("j"); //本日
$today = mktime(0,0,0,$month,$day,$year);

WHERE data1>$today AND data1<($today-24*60*60)

[天降大任于斯]

对本周的方法:

$week = date("W"); //本年的第几周,从星期一开始计算
$year = date("y"); //本年
$start = $week*7*24*60*60 + $year //本周初
$end = $start*7*24*60*60 //本周末
where data1>$start AND $data1<end

date()函数有个date("t")可以返回当前月份应有的天数,就分出了30天,29天,31天了
其他的就如前面的组合就可以
效率不高,但基本实现了需求
还有很多个时间函数

[pswdf]

本周的ＳＱＬ：

"SELECT * FROM table WHERE WEEK(FROM_UNIXTIME(`time1`, "%Y-%m-%d"),1) = ".intval(date("W"));

[gu1dai]

翻mysql手册啊，兄弟。

[cho__cho]

可不可以用MYSQL中的to_days()函数来查询，楼主只要求统计到天的

[zhys9]

$time = time();
今天的条件：data1 > strtotime(date('Ymd',$time))
本周的条件：data1 > strtotime(date('Ymd',$time))
本月的条件：data1 > strtotime(date('YmW',$time))

[free_kyy]

一定要唠叨啊

[phpeye]

如果能解决不用唠叨也行的，对吧，兄弟们！
如果不用mysql自带的函数那就怎么实现呢？
我的意思是本日，本周，本月，而不是一日，一周，一月．

[phpeye]

嗯,是我说的不清楚,我有罪!
我的意思是用$tt=time();产生秒数后再insert进表中的date1

[phpeye]

答案已经有人给出
但我们的讨论的意义已经超出了答案的意义.
作为这么一个日期问题的帖子应该对我这一阶段的和后来学习PHP的人有一个借鉴的机会.

谢谢所有参与的人!!

[ice_berg16]

case 1: //今天
$today = mktime(0, 0, 0, date("m"), date("d"), date("Y"));
$tmp .= (empty($tmp)?"WHERE":"AND") . " t.telTime > $today ";
break;
case 2: //昨天
$today = mktime(0, 0, 0, date("m"), date("d"), date("Y"));
$yestoday = mktime(0, 0, 0, date("m"), date("d")-1, date("Y"));
$tmp .= (empty($tmp)?"WHERE":"AND") . " t.telTime BETWEEN $yestoday AND $today ";
break;
case 3: //本星期
$w = date("w") == 0 ? 7 : date("w");
$thisweek = mktime(0, 0, 0, date("m"), date("d")-$w+1, date("Y"));
$tmp .= (empty($tmp)?"WHERE":"AND") . " t.telTime > $thisweek ";
break;
case 4: //本月
$thismonth = mktime(0, 0, 0, date("m"), 1, date("Y"));
$tmp .= (empty($tmp)?"WHERE":"AND") . " t.telTime > $thismonth ";
break;

[xuzuning]

“我是说表里面有这么一个字段是用来放time()的”

“我再补充一下吧
我有一个表,其中有一个时间字段叫date1,是time型的,即插入的时候是以time()插入的”

你已经出现概念性错误啦！
1、mysql中没有time函数，可见“用来放time()的”中的time()是php的函数（返回自1970年1月1日0时以来的秒数）
2、mysql中的time类型字段为时间字段，只保存时间而与日期无关

这两个东西并不等价，所以你的要求并不可能实现。

[lzkd]

也许我比较笨,还是没明白.楼主为什么想从time()中得到本周,本日等的流量?好象八杆子打不着呀

[fishfun123]

LZ的数据库里到底是什么内容? 为何不贴出一些数据作为参考呢?

[fishfun123]

假设LZ的date1为VARCHAR(32), 内容为
<?php $query="insert into db.tbl(date1) values('" . time() . "')"; ?>
下面的代码可求出date1的上限和下限.
<?php
$someday = strtotime("yesterday");

echo "Select a date: " . $someday . "(" . date("Y-m-d H:i:s", $someday) . ")";

//Total for one day or today
$time_from = mktime(0, 0, 0, date("m", $someday), date("d", $someday), date("Y", $someday));
$time_to = mktime(0, 0, 0, date("m", $someday), date("d", $someday) + 1, date("Y", $someday));
echo "</p>Total for one day or today";
echo "</br>" . $time_from . "(" . date("Y-m-d H:i:s", $time_from) . ")";
echo " - " . $time_to . "(" . date("Y-m-d H:i:s", $time_to) . ")";

//Total for one week or this week
//Week starts on Monday
$w = (date("w", $someday) == 0)? 7: date("w", $someday); //1-Monday, ... , 6-Saturday, 7-Sunday
$time_from = mktime(0, 0, 0, date("m", $someday), date("d", $someday) - $w + 1, date("Y", $someday));
$time_to = mktime(0, 0, 0, date("m", $someday), date("d", $someday) - $w + 8, date("Y", $someday));
echo "</p>Total for one week or this week - Week starts on Monday";
echo "</br>" . $time_from . "(" . date("Y-m-d H:i:s", $time_from) . ")";
echo " - " . $time_to . "(" . date("Y-m-d H:i:s", $time_to) . ")";

//Week starts on Sunday
$w = date("w", $someday); //1-Monday, ... , 6-Saturday, 0-Sunday
$time_from = mktime(0, 0, 0, date("m", $someday), date("d", $someday) - $w, date("Y", $someday));
$time_to = mktime(0, 0, 0, date("m", $someday), date("d", $someday) - $w + 7, date("Y", $someday));
echo "</p>Total for one week or this week - Week starts on Sunday";
echo "</br>" . $time_from . "(" . date("Y-m-d H:i:s", $time_from) . ")";
echo " - " . $time_to . "(" . date("Y-m-d H:i:s", $time_to) . ")";

//Total for one month or this month
$time_from = mktime(0, 0, 0, date("m", $someday), 1, date("Y", $someday));
$time_to = mktime(0, 0, 0, date("m", $someday) + 1, 1, date("Y", $someday));
echo "</p>Total for one month or this month";
echo "</br>" . $time_from . "(" . date("Y-m-d H:i:s", $time_from) . ")";
echo " - " . $time_to . "(" . date("Y-m-d H:i:s", $time_to) . ")";

//Total for one year or this year
$time_from = mktime(0, 0, 0, 1, 1, date("Y", $someday));
$time_to = mktime(0, 0, 0, 1, 1, date("Y", $someday) + 1);
echo "</p>Total for one year or this year";
echo "</br>" . $time_from . "(" . date("Y-m-d H:i:s", $time_from) . ")";
echo " - " . $time_to . "(" . date("Y-m-d H:i:s", $time_to) . ")";
?>

运行结果:
Select a date: 1173754062(2007-03-12 22:47:42)

Total for one day or today
1173672000(2007-03-12 00:00:00) - 1173758400(2007-03-13 00:00:00)

Total for one week or this week - Week starts on Monday
1173672000(2007-03-12 00:00:00) - 1174276800(2007-03-19 00:00:00)

Total for one week or this week - Week starts on Sunday
1173589200(2007-03-11 00:00:00) - 1174190400(2007-03-18 00:00:00)

Total for one month or this month
1172725200(2007-03-01 00:00:00) - 1175400000(2007-04-01 00:00:00)

Total for one year or this year
1167627600(2007-01-01 00:00:00) - 1199163600(2008-01-01 00:00:00)

[Meteorlet]

请确定你的date1是Int整型，而不是time字段
然后用以下SQL查询，没有测试过
当天：SELECT * FROM tbl WHERE TODAYS(FROM_UNIXTIME(date1)) = TODAYS(Now())
昨天：SELECT * FROM tbl WHERE TODAYS(FROM_UNIXTIME(date1)) = TODAYS(DATESUB(Now(), INTERVAL 1 DAY))
本周：SELECT * FROM tbl WHERE TODAYS(FROM_UNIXTIME(date1)) >= TODAYS(DATESUB(Now(), INTERVAL (DATE_FORMAT(FROM_UNIXTIME(date1), "%w") DAY)) AND TODAYS(FROM_UNIXTIME(date1)) <= TODAYS(DATEADD(Now(), INTERVAL (7 - DATE_FORMAT(FROM_UNIXTIME(date1), "%w")) DAY))
本月也是一样