Page 1 of 1
select date and time
Posted: Mon May 31, 2004 10:37 pm
by valen53
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.
Posted: Mon May 31, 2004 10:48 pm
by feyd
stab in the dark here:
SELECT * FROM `match` WHERE (mat_date >= '2004-06-01' AND mat_time >= '12:00') AND (mat_date <= '2004-06-02' AND mat_time <= '11:59')
Posted: Tue Jun 01, 2004 1:11 am
by valen53
feyd wrote:
SELECT * FROM `match` WHERE (mat_date >= '2004-06-01' AND mat_time >= '12:00') AND (mat_date <= '2004-06-02' AND mat_time <= '11:59')
i have try this sql b4, but won't work.
Posted: Tue Jun 01, 2004 3:47 am
by Buddha443556
Are you storing the time in 24 hour format? If so maybe you can concat the date and time and get a unix timestamp using UNIX_TIMESTAMP(date)? Or maybe use STR_TO_DATE(str,format) to get a DATETIME?
Posted: Tue Jun 01, 2004 4:15 am
by JAM
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).
Code: Select all
SELECT * FROM match WHERE unix_timestamp(mat_date) BETWEEN $startdate and $enddate
Posted: Tue Jun 01, 2004 4:48 am
by valen53
Code: Select all
SELECT * FROM match WHERE unix_timestamp(mat_date) BETWEEN $startdate and $enddate
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.

Posted: Tue Jun 01, 2004 10:04 am
by Buddha443556
valen53 wrote:Code: Select all
SELECT * FROM match WHERE unix_timestamp(mat_date) BETWEEN $startdate and $enddate
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.

Combining the two fields might be a good idea but there's also the SQL function CONCAT(str1,str2,...).
Maybe....
Code: Select all
SELECT * FROM match WHERE unix_timestamp(CONCAT(mat_date, ' ', mat_time)) BETWEEN $startdate and $enddate
That's a space in between your date and time fields. However, you may have to use STR_TO_DATE(str,format) if your mat_time is in AM/PM format.
Posted: Tue Jun 01, 2004 8:46 pm
by valen53
the SQL function CONCAT(str1,str2,...). was working.
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";
?>