I have a number of places that start at different times and close at different times.
To design their tables I have considered two fields as the Start time field and the end time field
But how do I choose type of fields?
So I can do some searches like this:
for example Places open between 8 and 10
And in this case, show me the location that opens at 8:15
If all you need is a 5 character field to display for instance 12:30, simply use 5 character varchar field. No need to make it more complicated than that.
https://dev.mysql.com/doc/refman/8.0/en/time.html
probably something similar in whatever platform you’re using if it’s not MySQL
nope… with VARCHAR, a search like this will fail –
BETWEEN '12:00' AND '01:00'
I can relate to wanting to have a date / time field in “human friendly” format. But doing so often means a lot of work will need to be done getting the values into “computer friendly” format to work with them and then changing them back to “human friendly” format. Can it be done? Sure. Is it efficient? No.
There are differences depending on the database used, but AFAIK, most have native date / time functions that work with date / time fields and come in quite handy.
Agreed @r937 I thought about that but I was hoping his needs were less than that. He needs datetime, then spend some time learning the sql parlance. I just KNEW someone was gonna call me out on it! haha! Good times!
Regarding the datetime thingy’s, because I use them so infrequently, I always dig back through 15 year old coding to ‘re-understand’ how to write a basic datetime query!
Haven’t written one in years though.
What I hate @Mittineague is seeing php time function outputs being stored in a database with a freakin varchar! But it’s not that difficult. While it’s a little more overhead, sticking with mysql datetime functions for this is king. Mysql has done an epic job with this over the years.
Thanks for the link. Jeez!
I swear, since the early 2000’s mysql’s online documentation has gone from technically lame to insane, to virtually needing a lawyer to read it, and even then it still conveys no real information to the newly adopted user.
‘Back in the day’, referring to mid-2000’s here, it was better suited to just ask google and look at other blogs referencing a particular select query.
I think the fork of MariaDB makes much sense and they are at least trying in the doc’s dept!
but with TIME it will also fail, won’t it? I don’t advocate VARCHAR but in this particular example VARCHAR and TIME will behave the same as long as we stick to a fixed length format like HH:MM.
This is a better example:
'2:30' BETWEEN '01:00' AND '03:00'
tested it… you’re right… instead of ‘01:00’ it should be ‘13:00’
Datetime or Time?
It depends on the nature of the places that start and close.
For something like a shop or bar that opens and closes the same time every day, I would go for Time because it’s not tied to a specific date.
But if it’s something like an event or festival that only happens on a particular day or just a few days, then maybe go for Datetime.
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.