想請問一下
我有一資料表message如下
想用"月"搜尋time欄位內的符合的資料
$start_time = date("Y-m",strtotime('-2 month'));
$end_time = date("Y-m",strtotime('-1 month'));$sql=mysql_query("select distinct title from message where time between '".$start_time."' and '".$end_time."' and unit '403' group order by title"); while($row=mysql_fenth_array($sql)){ echo "$row[title]"; }
但沒有任何輸出，該如何處理呢?
P.S用"天"搜尋time欄位可正常輸出
已邀請的邦友 {{ invite_list.length }}/5
把 Mysql 指令 echo 出來 +1
試試
$sql=mysql_query("select distinct title from message where time between '".$start_time."' and '".$end_time."' and unit='403' order by title");
2020/07/05 11:39 更新
再試試看
$sql=mysql_query("select distinct title from message where date_format(time,'%Y-%m') between '".$start_time."' and '".$end_time."' and unit='403' order by title");
以下是我的測試 script
CREATE TABLE message
(`time` timestamp, `title` varchar(7), `unit` int, `message` varchar(4))
;
INSERT INTO message
(`time`, `title`, `unit`, `message`)
VALUES
('2020-05-16 00:00:00', 'title01', 402, 'test'),
('2020-05-19 00:00:00', 'title02', 403, 'test'),
('2020-06-01 00:00:00', 'BPM', 403, 'test'),
('2020-06-12 00:00:00', '123', 302, 'test'),
('2020-06-19 00:00:00', 'title03', 201, 'test'),
('2020-04-18 00:00:00', 'title04', 306, '123'),
('2020-04-11 00:00:00', 'title05', 409, 'test'),
('2020-02-03 00:00:00', 'title06', 203, 'test')
;
select distinct title
from message
where time between '2020-05-01' and '2020-06-30'
and unit='403'
order by title;
select distinct title
from message
where date_format(time,'%Y-%m') between '2020-05' and '2020-06'
and unit='403'
order by title;
有的~我先用"天"搜尋
$start_time = date("Y-m-d",strtotime('-2 month'));
$end_time = date("Y-m-d",strtotime('-1 month'));
$sql = mysql_query("select distinct title from message2 where time between '".$start_time."' and '".$end_time."' and unit ='403' order by title");
while($list = mysql_fetch_array($sql)){
print_r($list[title]);
}
可得到ABC，但用"月"搜尋
$start_time = date("Y-m",strtotime('-2 month'));
$end_time = date("Y-m",strtotime('-1 month'));
$sql = mysql_query("select distinct title from message2 where time between '".$start_time."' and '".$end_time."' and unit ='403' order by title");
while($list = mysql_fetch_array($sql)){
print_r($list[title]);
}
沒有輸出到值
time 欄位裡是完整的日期/時間
如果你只用 Y-m (年月)去比較
自然不會落在 between 的區間中
大大有好的建議嗎?我想到的只有土法煉鋼-再多一欄位(time1)，在每次新增一筆資料時紀錄"年月"
再用sql time1 between
我的回答有新答案,請參考
....不要用 time1 這個點子
感謝大大幫忙，這方式讓我回想到上次的發文-浩瀚大提到的date_format
這裡
1.Php 執行 Mysql 前建議把 Mysql 指令 echo 出來
2.常用SQL語法教學
接著海綿寶寶以及rogeryao , 我另外做了些資料,提供參考.
create table it0705msg (
ts timestamp not null
, title varchar(7) not null
, unit int not null
, msg varchar(4) not null
);
insert into it0705msg
VALUES
('2020-05-16 00:00:00', 'title01', 402, 'test'),
('2020-05-19 00:00:00', 'title02', 403, 'test'),
('2020-06-01 00:00:00', 'BPM', 403, 'test'),
('2020-06-12 00:00:00', '123', 302, 'test'),
('2020-06-19 00:00:00', 'title03', 201, 'test'),
('2020-04-18 00:00:00', 'title04', 306, '123'),
('2020-04-11 00:00:00', 'title05', 409, 'test'),
('2020-02-03 00:00:00', 'title06', 203, 'test')
;
insert into it0705msg values
('2020-05-31 11:59:59', '測邊界', 200, 'test');
insert into it0705msg values
('2020-05-31 23:59:59', '測邊界2', 400, 'test');
-- 假設要選2020年5月區間的 , 我們可以產生2020年5月的開始的 timestamp
-- 先產生以下幾種格式做測試觀察用
select timestamp '2020-05-01 00:00:00' as start_ts
, date_add('2020-05-01', interval 1 month) as end_date
, convert(date_add('2020-05-01', interval 1 month), datetime) end_dt;
+---------------------+------------+---------------------+
| start_ts | end_date | end_dt |
+---------------------+------------+---------------------+
| 2020-05-01 00:00:00 | 2020-06-01 | 2020-06-01 00:00:00 |
+---------------------+------------+---------------------+
1 row in set (0.00 sec)
select *
from it0705msg
where ts between timestamp '2020-05-01 00:00:00'
and convert(date_add('2020-05-01', interval 1 month), datetime);
+---------------------+------------+------+------+
| ts | title | unit | msg |
+---------------------+------------+------+------+
| 2020-05-16 00:00:00 | title01 | 402 | test |
| 2020-05-19 00:00:00 | title02 | 403 | test |
| 2020-06-01 00:00:00 | BPM | 403 | test |
| 2020-05-31 11:59:59 | 測邊界 | 200 | test |
| 2020-05-31 23:59:59 | 測邊界2 | 400 | test |
+---------------------+------------+------+------+
5 rows in set (0.00 sec)
-- 這裡有觀察到 2020-06-01 00:00:00 的資料出現了.
-- 我們可以利用另外的函數 last_day(date) 來獲得該月的最後一天.然後再人工補 23:59:59
-- 或者是 add_month()結果 的減一秒.
select date_sub(date_add('2020-05-01', interval 1 month), interval 1 second) as end_dt;
+---------------------+
| end_dt |
+---------------------+
| 2020-05-31 23:59:59 |
+---------------------+
select *
from it0705msg
where ts between timestamp '2020-05-01 00:00:00'
and date_sub(date_add('2020-05-01', interval 1 month), interval 1 second);
+---------------------+------------+------+------+
| ts | title | unit | msg |
+---------------------+------------+------+------+
| 2020-05-16 00:00:00 | title01 | 402 | test |
| 2020-05-19 00:00:00 | title02 | 403 | test |
| 2020-05-31 11:59:59 | 測邊界 | 200 | test |
| 2020-05-31 23:59:59 | 測邊界2 | 400 | test |
+---------------------+------------+------+------+
4 rows in set (0.00 sec)
-- 其實使用 datetime 格式 就比較方便啦.範圍也比較廣.
https://dev.mysql.com/doc/refman/8.0/en/datetime.html
the range for DATETIME values is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999', and the range for TIMESTAMP values is '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'.
The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
iThome鐵人賽
看影片追技術