mysql 如何得到一年当中每周第一秒的时间戳? 比如,给定200951代表2009年的第51个周,我想通过这个值得到本周第一秒的时间戳,能做到吗? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 你到底是要本周,还是要200923这一周的timestamp ?你所说的时间戳 是指TIMESTAMP还是UNIX——TIME?另外本周第一天是周几?周日?周一?问题说明越详细,回答也会越准确!参见如何提问。(提问的智慧) mysql> select CURDATE(),CURDATE()-interval WEEKDAY(CURDATE()) day;+------------+-------------------------------------------+| CURDATE() | CURDATE()-interval WEEKDAY(CURDATE()) day |+------------+-------------------------------------------+| 2009-12-24 | 2009-12-21 |+------------+-------------------------------------------+1 row in set (0.00 sec)mysql> select UNIX_TIMESTAMP(CURDATE()-interval WEEKDAY(CURDATE()) day);+-----------------------------------------------------------+| UNIX_TIMESTAMP(CURDATE()-interval WEEKDAY(CURDATE()) day) |+-----------------------------------------------------------+| 1261324800 |+-----------------------------------------------------------+1 row in set (0.00 sec)mysql> 你到底是要本周,还是要200923这一周的timestamp ? 是200923这一周的timestamp,给参数200923,得到这周起始那一时刻的timestamp.你所说的时间戳 是指TIMESTAMP还是UNIX——TIME?是timestamp, 从1970-01-01开始的描述,类似于这样的1261624259.另外本周第一天是周几?周日?周一? 这个无所谓,最好是周一!问题说明越详细,回答也会越准确!参见如何提问。(提问的智慧)谢谢版主提醒! 我需要的就是用程序实现这个,版主现在让我给出,还好,我刚拿shell脚本写了一个:#!/bin/shstart_date='2009-01-01'end_date='2010-06-01'date=$start_dateweek=-1while truedo if test $date = $end_date then exit fi curweek=`date -d "$date" +%Y%W` if test ! $week -eq $curweek then tm=`date -d "$date" +%s` echo $curweek $date $tm week=$curweek #sleep 1 fi date=`date -d "$date + 1 day" +%Y-%m-%d`done以上程序列出从2009-01-01到2010-06-01的每个周一以及对应的日期和时间戳:200900 2009-01-01 1230739200200901 2009-01-05 1231084800200902 2009-01-12 1231689600200903 2009-01-19 1232294400200904 2009-01-26 1232899200200905 2009-02-02 1233504000200906 2009-02-09 1234108800200907 2009-02-16 1234713600200908 2009-02-23 1235318400200909 2009-03-02 1235923200200910 2009-03-09 1236528000200911 2009-03-16 1237132800200912 2009-03-23 1237737600200913 2009-03-30 1238342400200914 2009-04-06 1238947200200915 2009-04-13 1239552000200916 2009-04-20 1240156800200917 2009-04-27 1240761600200918 2009-05-04 1241366400200919 2009-05-11 1241971200200920 2009-05-18 1242576000200921 2009-05-25 1243180800200922 2009-06-01 1243785600200923 2009-06-08 1244390400200924 2009-06-15 1244995200200925 2009-06-22 1245600000200926 2009-06-29 1246204800200927 2009-07-06 1246809600200928 2009-07-13 1247414400200929 2009-07-20 1248019200200930 2009-07-27 1248624000200931 2009-08-03 1249228800200932 2009-08-10 1249833600200933 2009-08-17 1250438400200934 2009-08-24 1251043200200935 2009-08-31 1251648000200936 2009-09-07 1252252800200937 2009-09-14 1252857600200938 2009-09-21 1253462400200939 2009-09-28 1254067200200940 2009-10-05 1254672000200941 2009-10-12 1255276800200942 2009-10-19 1255881600200943 2009-10-26 1256486400200944 2009-11-02 1257091200200945 2009-11-09 1257696000200946 2009-11-16 1258300800200947 2009-11-23 1258905600200948 2009-11-30 1259510400200949 2009-12-07 1260115200200950 2009-12-14 1260720000200951 2009-12-21 1261324800200952 2009-12-28 1261929600201000 2010-01-01 1262275200201001 2010-01-04 1262534400201002 2010-01-11 1263139200201003 2010-01-18 1263744000201004 2010-01-25 1264348800201005 2010-02-01 1264953600201006 2010-02-08 1265558400201007 2010-02-15 1266163200201008 2010-02-22 1266768000201009 2010-03-01 1267372800201010 2010-03-08 1267977600201011 2010-03-15 1268582400201012 2010-03-22 1269187200201013 2010-03-29 1269792000201014 2010-04-05 1270396800201015 2010-04-12 1271001600201016 2010-04-19 1271606400201017 2010-04-26 1272211200201018 2010-05-03 1272816000201019 2010-05-10 1273420800201020 2010-05-17 1274025600201021 2010-05-24 1274630400201022 2010-05-31 1275235200 mysql> set @x=200923;Query OK, 0 rows affected (0.00 sec)mysql> select @x, UNIX_TIMESTAMP(DATE_ADD((floor(@x/100)*10000+101),INTERVAL (@x%100-1)*7+(7-weekday(floor(@x/100)*10000+101))%7 DAY)) as k;+--------+------------+| @x | k |+--------+------------+| 200923 | 1244390400 |+--------+------------+1 row in set (0.00 sec)mysql> select @x, UNIX_TIMESTAMP(DATE_ADD((floor(@x/100)*10000+101),INTERVAL (@x%100-1)*7+(7-weekday(floor(@x/100)*10000+101))%7 DAY)) as k;+--------+------------+| @x | k |+--------+------------+| 201004 | 1264348800 |+--------+------------+1 row in set (0.00 sec)mysql> mysql存储过程奇怪错误 多对多关系~ 我想问下为什么我的mysql front不能上到Mysql服务器连接127.0.0.1 为什么我的update没用 mysql 枚举类型中的汉字问题 Mysql奇怪的慢查询 请教在bin下执行./mysqldump -u someone -p db < db.sql和./mysql -u someone -p db < db.sql的区别 用MYSQL的ibdata1文件,如何恢复数据? 怎么查询总分在前15名的信息,并按总分降序排列? 关于GROUP的查询优化方式 单表找出父子关系的记录 高手进来解决下mysql查询语句的编辑问题
+------------+-------------------------------------------+
| CURDATE() | CURDATE()-interval WEEKDAY(CURDATE()) day |
+------------+-------------------------------------------+
| 2009-12-24 | 2009-12-21 |
+------------+-------------------------------------------+
1 row in set (0.00 sec)mysql> select UNIX_TIMESTAMP(CURDATE()-interval WEEKDAY(CURDATE()) day);
+-----------------------------------------------------------+
| UNIX_TIMESTAMP(CURDATE()-interval WEEKDAY(CURDATE()) day) |
+-----------------------------------------------------------+
| 1261324800 |
+-----------------------------------------------------------+
1 row in set (0.00 sec)mysql>
是200923这一周的timestamp,给参数200923,得到这周起始那一时刻的timestamp.你所说的时间戳 是指TIMESTAMP还是UNIX——TIME?
是timestamp, 从1970-01-01开始的描述,类似于这样的1261624259.另外本周第一天是周几?周日?周一?
这个无所谓,最好是周一!问题说明越详细,回答也会越准确!参见如何提问。(提问的智慧)
谢谢版主提醒!
我需要的就是用程序实现这个,版主现在让我给出,还好,我刚拿shell脚本写了一个:#!/bin/shstart_date='2009-01-01'
end_date='2010-06-01'
date=$start_date
week=-1while true
do
if test $date = $end_date
then
exit
fi curweek=`date -d "$date" +%Y%W`
if test ! $week -eq $curweek
then
tm=`date -d "$date" +%s`
echo $curweek $date $tm
week=$curweek
#sleep 1
fi
date=`date -d "$date + 1 day" +%Y-%m-%d`
done
以上程序列出从2009-01-01到2010-06-01的每个周一以及对应的日期和时间戳:200900 2009-01-01 1230739200
200901 2009-01-05 1231084800
200902 2009-01-12 1231689600
200903 2009-01-19 1232294400
200904 2009-01-26 1232899200
200905 2009-02-02 1233504000
200906 2009-02-09 1234108800
200907 2009-02-16 1234713600
200908 2009-02-23 1235318400
200909 2009-03-02 1235923200
200910 2009-03-09 1236528000
200911 2009-03-16 1237132800
200912 2009-03-23 1237737600
200913 2009-03-30 1238342400
200914 2009-04-06 1238947200
200915 2009-04-13 1239552000
200916 2009-04-20 1240156800
200917 2009-04-27 1240761600
200918 2009-05-04 1241366400
200919 2009-05-11 1241971200
200920 2009-05-18 1242576000
200921 2009-05-25 1243180800
200922 2009-06-01 1243785600
200923 2009-06-08 1244390400
200924 2009-06-15 1244995200
200925 2009-06-22 1245600000
200926 2009-06-29 1246204800
200927 2009-07-06 1246809600
200928 2009-07-13 1247414400
200929 2009-07-20 1248019200
200930 2009-07-27 1248624000
200931 2009-08-03 1249228800
200932 2009-08-10 1249833600
200933 2009-08-17 1250438400
200934 2009-08-24 1251043200
200935 2009-08-31 1251648000
200936 2009-09-07 1252252800
200937 2009-09-14 1252857600
200938 2009-09-21 1253462400
200939 2009-09-28 1254067200
200940 2009-10-05 1254672000
200941 2009-10-12 1255276800
200942 2009-10-19 1255881600
200943 2009-10-26 1256486400
200944 2009-11-02 1257091200
200945 2009-11-09 1257696000
200946 2009-11-16 1258300800
200947 2009-11-23 1258905600
200948 2009-11-30 1259510400
200949 2009-12-07 1260115200
200950 2009-12-14 1260720000
200951 2009-12-21 1261324800
200952 2009-12-28 1261929600
201000 2010-01-01 1262275200
201001 2010-01-04 1262534400
201002 2010-01-11 1263139200
201003 2010-01-18 1263744000
201004 2010-01-25 1264348800
201005 2010-02-01 1264953600
201006 2010-02-08 1265558400
201007 2010-02-15 1266163200
201008 2010-02-22 1266768000
201009 2010-03-01 1267372800
201010 2010-03-08 1267977600
201011 2010-03-15 1268582400
201012 2010-03-22 1269187200
201013 2010-03-29 1269792000
201014 2010-04-05 1270396800
201015 2010-04-12 1271001600
201016 2010-04-19 1271606400
201017 2010-04-26 1272211200
201018 2010-05-03 1272816000
201019 2010-05-10 1273420800
201020 2010-05-17 1274025600
201021 2010-05-24 1274630400
201022 2010-05-31 1275235200
Query OK, 0 rows affected (0.00 sec)mysql> select @x, UNIX_TIMESTAMP(DATE_ADD((floor(@x/100)*10000+101),INTERVAL (@
x%100-1)*7+(7-weekday(floor(@x/100)*10000+101))%7 DAY)) as k;
+--------+------------+
| @x | k |
+--------+------------+
| 200923 | 1244390400 |
+--------+------------+
1 row in set (0.00 sec)mysql> select @x, UNIX_TIMESTAMP(DATE_ADD((floor(@x/100)*10000+101),INTERVAL (@
x%100-1)*7+(7-weekday(floor(@x/100)*10000+101))%7 DAY)) as k;
+--------+------------+
| @x | k |
+--------+------------+
| 201004 | 1264348800 |
+--------+------------+
1 row in set (0.00 sec)mysql>