select date and time

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
valen53
Forum Contributor
Posts: 137
Joined: Tue Aug 27, 2002 9:29 am

select date and time

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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')
valen53
Forum Contributor
Posts: 137
Joined: Tue Aug 27, 2002 9:29 am

Post 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.
User avatar
Buddha443556
Forum Regular
Posts: 873
Joined: Fri Mar 19, 2004 1:51 pm

Post 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?
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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
valen53
Forum Contributor
Posts: 137
Joined: Tue Aug 27, 2002 9:29 am

Post 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. :?
User avatar
Buddha443556
Forum Regular
Posts: 873
Joined: Fri Mar 19, 2004 1:51 pm

Post 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.
valen53
Forum Contributor
Posts: 137
Joined: Tue Aug 27, 2002 9:29 am

Post 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"; 
?>
Post Reply