2

Lets say shop is working from 8:00 till 23:00 and we use time format. Then it's easy. Some kind of:

where NOW() > start and NOW() < end

But what if shop working until 1:00am next day? And now exactly 23:00; So 23 > 1. This is will not gonna work.

So how to store and search business time in the correct way? Maybe in the end field better to store difference in seconds or i even don't know...

UPD: If you recommend use timestamp, then how i will find this time after one year, for example? We need to convert all dates to one?

The only solution that i decided use for now. 

select * from times where ('05:00:00' between opens::time and closes::time) or ( closes::time < opens::time and '05:00:00' >= opens::time and '05:00:00' > closes::time ) or ( closes::time < opens::time and opens::time > '05:00:00' and closes::time > '05:00:00' ) and dow = 4

So for 13:00:00 - 04:00:00 I have results when variable is:

  • 05:00:00 - no results
  • 12:00:00 - no results
  • 00:00:00 - 1 row
  • 01:00:00 - 1 row
  • 18:00:00 - 1 row

If you have any better idea, please share

Improve this question
5
  • you can have three columns: opens::time, closes::time, dow::int Commented Aug 10, 2017 at 13:26
  • Is it mySQL or Postgres? Not a good idea to just use time format. Store it as a timestamp and extract just the time part as per your need. You'll have serious compatibility issues. Redshift (based on Postgres) doesn't support time as a datatype. Commented Aug 10, 2017 at 13:43
  • @YusufHassan Okay, do you have any idea how it should work with timestamp? If i'll save that monday opens as 2017-08-11 04:00:00. How i could find it in 2018? Commented Aug 10, 2017 at 17:52
  • NOW() is a datetime, not just an hour. Commented Aug 17, 2017 at 21:55
  • @RickJames Yes. But i need to find a specific time, not a date. Only problem when it's next day. Commented Aug 18, 2017 at 9:58

4 Answers 4

Reset to default
2

The only correct way to store business hours is to use iCalendar RRules and ExDates

Store the rules a table. Use a library (Postgres has a few) to generate opening hours for the upcoming year. Use a materialized view for this

This lets you handle things like holidays, being closed on the last Thursday of every month, etc.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Yes, this is a very good idea to add exclude dates function. But see, here's the problem. I cannot generate all 365. Because there website not only for one shop. Here could be a millions of them.
0

Its a little bit unclear what language you are in. But here are some examples. If it is formattet as dateTime from the server to example C#, then you can use: start> date1 && end < date2.

If using MySQL then check this post: MySQL "between" clause not inclusive?

Improve this answer

1 Comment

How it could be datetime? If i need to set time exactly for monday, not for 1 August. That's the problem exactly.
0 
t=# create table shop(i int, opens time, closes time, dow int); CREATE TABLE t=# insert into shop select g+10,'11:00','23:00', g from generate_series(1,5,1) g; INSERT 0 5 t=# insert into shop select 23,'12:00','13:00', 6; INSERT 0 1

then your logic would work:

t=# select * from shop where now()::time between opens and closes and extract(dow from now())::int = dow; i | opens | closes | dow ----+----------+----------+----- 14 | 11:00:00 | 23:00:00 | 4 (1 row)

it is open on Thursday ATM.

and example for Satruday on and not on time:

t=# select * from shop where '2017-08-12 12:59'::time between opens and closes and extract(dow from '2017-08-12 12:59'::timestamp)::int = dow; i | opens | closes | dow ----+----------+----------+----- 23 | 12:00:00 | 13:00:00 | 6 (1 row) Time: 0.240 ms t=# select * from shop where '2017-08-12 13:01'::time between opens and closes and extract(dow from '2017-08-12 13:01'::timestamp)::int = dow; i | opens | closes | dow ---+-------+--------+----- (0 rows)
Improve this answer

3 Comments

Explain please what is "dow". And what will happen if we will try find time between 8:00 am and 2:00am. Where 2:00am is end time. How search engine will understand that is the next day?
dow - day of week. if you specify the timestamp - it will get the dow of week of that timestamp.
And now i realise that this is not helps with main problem - when time on next day. :( Not working @Vao Tsun
0

You should use TIMESTAMP as data_type for start and end column. then you can use where NOW() > start and NOW() < end. It will work fine.

Improve this answer

6 Comments

Let's say i save for 09/11/2017 8:00 and 09/11/2017 8:00. How could i find that shop is open if today is December 31? Sorry, for dumb question, but i really need an explanation
You have to update your shop start and end date-time in Data base.
during query? Could you provide and example? I can't find . anything useful. @Raushan
You have to save every days start and end time of shop in database in TIMESTAMP format. Now, If you want to find for a day shop was opened or no then you can find using query like this : SELECT * FROM TimeTable WHERE (NOW() BETWEEN start AND end )
here not only one shop. Also no one company do not want make so many settings. And also how to edit then? Sorry, but this is wrong solution, at least for me. I understand your idea, but no.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.