select date and time
Moderator: General Moderators
select date and time
i want to select record base on date and time. like..
2004-06-01 12:00pm until 2004-06-02 11:59am.
my date and time was different field. if i select like
<?php
SELECT * FROM `match` WHERE mat_date >= '2004-06-01' and mat_time >= '12:00'
?>
only records of the day and more than 12 will select out.
anybody have idea to solve it? thank for reply
i use MySQL 4.0.. so cannot use subquery.
2004-06-01 12:00pm until 2004-06-02 11:59am.
my date and time was different field. if i select like
<?php
SELECT * FROM `match` WHERE mat_date >= '2004-06-01' and mat_time >= '12:00'
?>
only records of the day and more than 12 will select out.
anybody have idea to solve it? thank for reply
i use MySQL 4.0.. so cannot use subquery.
- Buddha443556
- Forum Regular
- Posts: 873
- Joined: Fri Mar 19, 2004 1:51 pm
As Buddha443556 mention, unix_timestamp might be interesting.
You 'could' use gmmktime() or similiar php function to convert both start and end time (looking like 2004-06-01 12:00pm) to a unix timestamp, then use that in the sql similiar as shown below. It's all about how you get those dates in the first time (userform input, hardcoded into the script and so on).
You 'could' use gmmktime() or similiar php function to convert both start and end time (looking like 2004-06-01 12:00pm) to a unix timestamp, then use that in the sql similiar as shown below. It's all about how you get those dates in the first time (userform input, hardcoded into the script and so on).
Code: Select all
SELECT * FROM match WHERE unix_timestamp(mat_date) BETWEEN $startdate and $enddateCode: Select all
SELECT * FROM match WHERE unix_timestamp(mat_date) BETWEEN $startdate and $enddate- Buddha443556
- Forum Regular
- Posts: 873
- Joined: Fri Mar 19, 2004 1:51 pm
Combining the two fields might be a good idea but there's also the SQL function CONCAT(str1,str2,...).valen53 wrote:But now is mat_date and mat_time was separately field. unless i combine these 2 field together. But need to edit a lot of code.Code: Select all
SELECT * FROM match WHERE unix_timestamp(mat_date) BETWEEN $startdate and $enddate
Maybe....
Code: Select all
SELECT * FROM match WHERE unix_timestamp(CONCAT(mat_date, ' ', mat_time)) BETWEEN $startdate and $enddatethe SQL function CONCAT(str1,str2,...). was working.
thank u so much ..
thank u so much ..
Code: Select all
<?php
$SomeDate='2004-06-01';
$SomeTime='12:00';
$SomeDateTime=$SomeDate.$SomeTime
$SQL="SELECT * FROM `match` WHERE CONCAT(mat_date,mat_time)>=$SomeDateTime";
?>