唠叨帮忙喔 php mysql的日期问题 只有"date1 是用来放秒的"这个条件?LZ这个写错了吧?有这样的存法?mktime,strtotime这些方法可以试试! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 我是说表里面有这么一个字段是用来放time()的 FROM_UNIXTIME(unix_timestamp,format) 返回表示 Unix 时间标记的一个字符串,根据format字符串格式化。format可以包含与DATE_FORMAT()函数列出的条目同样的修饰符。 mysql> select FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x'); -> '1997 23rd December 03:43:30 x'mysql 的 UNIX_TIMESTAMP() 函数与 php 的 time() 函数是等价的 我的date1是秒记的唠叨能不能说个例子? 我再补充一下吧我有一个表,其中有一个时间字段叫date1,是time型的,即插入的时候是以time()插入的我现在需要从这表里的数据做统计就是如何统计如"今日" "本周" "本月"这样的访问量,我这样说不知道大家明白没有?谢谢关注 今天的条件:data1>time()-24*60*60本周的条件:data1>time()-24*60*60*7本月的条件:data1>time()-24*60*60*30 mysql有很多时间函数,查一下比如当月:select * from table where month(date_format(now(),'%Y-%m-%d')) = month(date_format(date1,'%Y-%m-%d')); seared2008你这个不行呀,你这是一天,一周,一个月的,而且一个月不一定都是30天的,呵呵 sorry.little change.select * from table where month(from_unixtime(unix_timestamp(),'%Y-%m-%d')) = month(from_unixtime(date1,'%Y-%m-%d')); <?$tt=time();echo "本日=".date("d", $tt);echo "本月=".date("m", $tt);echo "本周=".date("l", $tt);?> 本日=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<enddate()函数有个date("t")可以返回当前月份应有的天数,就分出了30天,29天,31天了其他的就如前面的组合就可以效率不高,但基本实现了需求还有很多个时间函数 zeroleonhart,给点例子看看呢,好吗 本周的SQL:"SELECT * FROM table WHERE WEEK(FROM_UNIXTIME(`time1`, "%Y-%m-%d"),1) = ".intval(date("W")); 可不可以用MYSQL中的to_days()函数来查询,楼主只要求统计到天的 $time = time();今天的条件:data1 > strtotime(date('Ymd',$time))本周的条件:data1 > strtotime(date('Ymd',$time))本月的条件:data1 > strtotime(date('YmW',$time)) 如果能解决不用唠叨也行的,对吧,兄弟们!如果不用mysql自带的函数那就怎么实现呢?我的意思是本日,本周,本月,而不是一日,一周,一月. 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; “我是说表里面有这么一个字段是用来放time()的”“我再补充一下吧我有一个表,其中有一个时间字段叫date1,是time型的,即插入的时候是以time()插入的”你已经出现概念性错误啦!1、mysql中没有time函数,可见“用来放time()的”中的time()是php的函数(返回自1970年1月1日0时以来的秒数)2、mysql中的time类型字段为时间字段,只保存时间而与日期无关这两个东西并不等价,所以你的要求并不可能实现。 也许我比较笨,还是没明白.楼主为什么想从time()中得到本周,本日等的流量?好象八杆子打不着呀 假设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 today1173672000(2007-03-12 00:00:00) - 1173758400(2007-03-13 00:00:00)Total for one week or this week - Week starts on Monday1173672000(2007-03-12 00:00:00) - 1174276800(2007-03-19 00:00:00)Total for one week or this week - Week starts on Sunday1173589200(2007-03-11 00:00:00) - 1174190400(2007-03-18 00:00:00)Total for one month or this month1172725200(2007-03-01 00:00:00) - 1175400000(2007-04-01 00:00:00)Total for one year or this year1167627600(2007-01-01 00:00:00) - 1199163600(2008-01-01 00:00:00) 请确定你的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))本月也是一样 嗯,是我说的不清楚,我有罪!我的意思是用$tt=time();产生秒数后再insert进表中的date1 答案已经有人给出但我们的讨论的意义已经超出了答案的意义.作为这么一个日期问题的帖子应该对我这一阶段的和后来学习PHP的人有一个借鉴的机会.谢谢所有参与的人!! 替换字符串的问题 PHP查询数据库数据时出现错误 关于nginx伪静态问题 excel_class 导入导出的扩展 还是算法问题!急急急! 我看分页类 请大家发表意见 好让我学得更好 discuz登陆为何总是丢失? 小问题.如何显示MYSQL里带换行的内容?急 急 寻找用PHP做ZIP压缩文件的程序 急问!! smarty 非常棘手的问题!!! 在线等~~ 感谢 子目录下的页面无法在浏览器中显示?
返回表示 Unix 时间标记的一个字符串,根据format字符串格式化。format可以包含与DATE_FORMAT()函数列出的条目同样的修饰符。
mysql> select FROM_UNIXTIME(UNIX_TIMESTAMP(),
'%Y %D %M %h:%i:%s %x');
-> '1997 23rd December 03:43:30 x'
mysql 的 UNIX_TIMESTAMP() 函数与 php 的 time() 函数是等价的
唠叨能不能说个例子?
我有一个表,其中有一个时间字段叫date1,是time型的,即插入的时候是以time()插入的我现在需要从这表里的数据做统计
就是如何统计如"今日" "本周" "本月"
这样的访问量,我这样说不知道大家明白没有?
谢谢关注
本周的条件:data1>time()-24*60*60*7
本月的条件:data1>time()-24*60*60*30
比如当月:select * from table where month(date_format(now(),'%Y-%m-%d')) = month(date_format(date1,'%Y-%m-%d'));
你这个不行呀,你这是一天,一周,一个月的,而且一个月不一定都是30天的,呵呵
select * from table where month(from_unixtime(unix_timestamp(),'%Y-%m-%d')) = month(from_unixtime(date1,'%Y-%m-%d'));
$tt=time();
echo "本日=".date("d", $tt);
echo "本月=".date("m", $tt);
echo "本周=".date("l", $tt);
?>
本月=03
本周=Thursday这样真的可以吗?
$month = date("n"); //本月
$day = date("j"); //本日
$today = mktime(0,0,0,$month,$day,$year);WHERE data1>$today AND data1<($today-24*60*60)
$year = date("y"); //本年
$start = $week*7*24*60*60 + $year //本周初
$end = $start*7*24*60*60 //本周末
where data1>$start AND $data1<enddate()函数有个date("t")可以返回当前月份应有的天数,就分出了30天,29天,31天了
其他的就如前面的组合就可以
效率不高,但基本实现了需求
还有很多个时间函数
可不可以用MYSQL中的to_days()函数来查询,楼主只要求统计到天的
今天的条件:data1 > strtotime(date('Ymd',$time))
本周的条件:data1 > strtotime(date('Ymd',$time))
本月的条件:data1 > strtotime(date('YmW',$time))
如果不用mysql自带的函数那就怎么实现呢?
我的意思是本日,本周,本月,而不是一日,一周,一月.
$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;
我有一个表,其中有一个时间字段叫date1,是time型的,即插入的时候是以time()插入的”你已经出现概念性错误啦!
1、mysql中没有time函数,可见“用来放time()的”中的time()是php的函数(返回自1970年1月1日0时以来的秒数)
2、mysql中的time类型字段为时间字段,只保存时间而与日期无关这两个东西并不等价,所以你的要求并不可能实现。
<?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)
然后用以下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))
本月也是一样
我的意思是用$tt=time();产生秒数后再insert进表中的date1
但我们的讨论的意义已经超出了答案的意义.
作为这么一个日期问题的帖子应该对我这一阶段的和后来学习PHP的人有一个借鉴的机会.谢谢所有参与的人!!