Page 1 of 1

about selecting rows

Posted: Wed Aug 18, 2004 2:40 am
by bugthefixer
i have a table which has three columns

station_id,arrival_time,departure_time

now the sample data is like this

Code: Select all

st_id    dep_time    arr_time
23       11:10       11:20
25       1:30        1:40
45       6:20        6:45
31       10:40       10:45
34       12:20       12:30
21       4:10        4:15
now if the current time is 1:45 then i want to search through the table the most nearest but less departure time like in this case iy should give result

1:40 and
12:30

it gives 12:30 also because 1:45 time occurs twice in this time scheme
the first occurance gives 1:40 and if there is second occurance then it also gives second nearest i.e. 12:30 and if there is a third occurance then it should also give third nearest time.

any1 can tell me wat query shud i use or wat is the strategy to get the desired result.

Posted: Wed Aug 18, 2004 3:23 am
by feyd
does anyone else get a flashback of junior high algebra? "two trains leave stations heading in opposite directions....." :P

these times would require 24hr formatting and losing the : .. better yet, store those times as timestamps..

then it's just

Code: Select all

select * from tablename where dep_time <= 'current_time'
where current_time is the value you wish to compare against..

Posted: Wed Aug 18, 2004 3:47 am
by bugthefixer
well i m sorry i interchanged the arrival and departure time heading..

actually i m using 24 hr format i was just trying to give u an example but a train travels even two days so it is possible for a train to be on two locations at the same(infact these will be two trains with the same name; so we have to consider them as single train).

now the problem is i cant store time in timestamp format because in this way new probs will arise..

isnt there any other way.

Posted: Wed Aug 18, 2004 4:12 am
by feyd
if you're using one of the time types, you could use the mysql time comparison functions:

http://dev.mysql.com/doc/mysql/en/Date_ ... tions.html