iT邦幫忙

0

mysql語法的應用

ektrontek 1 月前5342 瀏覽

請教大家
如果在mysql資料表中,要如何利用語法抓出當月份的資料呢?如下圖timemodified:1473745083為日期欄位,要如何顯示當月份與上個月分的資料?煩請大家指點,謝謝
http://ithelp.ithome.com.tw/upload/images/20170314/20003705U557pTZNeH.jpg

evio0502 iT邦新手 4 級 ‧ 1 月前 檢舉
佐證資訊太少~反推時間的變數不足
1.是否寄到時分秒
2.請提供另一筆不同日期的資訊~

2 個回答

2
bizpro
iT邦大師 1 級 ‧ 1 月前
最佳解答

假設:

DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `timemodified` INT(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO table1 values(NULL,UNIX_TIMESTAMP(NOW()));
INSERT INTO table1 values(NULL,UNIX_TIMESTAMP(NOW())-8640000);
INSERT INTO table1 values(NULL,UNIX_TIMESTAMP(NOW())-1864000);
INSERT INTO table1 values(NULL,UNIX_TIMESTAMP(NOW())-2864000);

全部資料:
select id,timemodified,FROM_UNIXTIME(timemodified) from table1;
結果:

id timemodified FROM_UNIXTIME(timemodified)
1 1489473060 March, 14 2017 06:31:00
2 1480833060 December, 04 2016 06:31:00
3 1487609060 February, 20 2017 16:44:20
4 1486609060 February, 09 2017 02:57:40

本月: 第一天是前推一個月加一天, 最後一天是本月的最後一天
select id,timemodified,FROM_UNIXTIME(timemodified) from table1 t where FROM_UNIXTIME(t.timemodified) >= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)), INTERVAL 1 DAY) and
FROM_UNIXTIME(t.timemodified) <= LAST_DAY(NOW());
結果:

id timemodified FROM_UNIXTIME(timemodified)
1 1489473060 March, 14 2017 06:31:00

上月: 第一天是前推二個月最後一天加一天, 最後一天是前推一個月的最後一天
select id,timemodified,FROM_UNIXTIME(timemodified) from table1 t
where FROM_UNIXTIME(t.timemodified) >= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH)), INTERVAL 1 DAY) and
FROM_UNIXTIME(t.timemodified) <= LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH));
結果:

id timemodified FROM_UNIXTIME(timemodified)
3 1487609060 February, 20 2017 16:44:20
4 1486609060 February, 09 2017 02:57:40
2
wiseguy
iT邦超人 1 級 ‧ 1 月前

假設本月份是三月,要查本月份的資料就是日期時間落在 2017-03-01 00:00:00 及 2017-03-31 23:59:59 之間的資料。因此你的 SQL 會是類似
select * from TABLE where timemodified between UNIX_TIMESTAMP('2017-03-01 00:00:00') and UNIX_TIMESTAMP('2017-03-31 23:59:59');

我要發表回答

立即登入回答