alter "history" table - Githubissues

cyklokoalicia / OpenSourceBikeShare

The world's first low-cost and open source bike sharing system. (new version in development, use working "breakthrough" release instead!)

http://opensourcebikeshare.com/

GNU General Public License v3.0

168 stars 71 forks source link

alter "history" table #11

Open mmmaly opened 10 years ago

mmmaly commented 10 years ago

create a separate column for stand (both for return and rent). This is useful for making queries regarding stand usage; and to implement the "stack".
create a separate column linking this action to a previous action (e.g. time, or ID). This is useful to make queries which evaluate duration of various events.

mmmaly commented 10 years ago

after altering, it is possible to fill the columns also for past actions

SELECT h1.*,max(h2.time) as renttime FROM history as h1 join history as h2 where h1.action="return" and h2.action="rent" and h2.bikenum=h1.bikenum and h2.time<h1.time group by h1.time order by h1.time desc LIMIT 500