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

Fortael
  • 322
  • 2
  • 15
  • you can have three columns: opens::time, closes::time, dow::int – Vao Tsun Aug 10 '17 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. – Yusuf Hassan Aug 10 '17 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? – Fortael Aug 10 '17 at 17:52
  • `NOW()` is a datetime, _not_ just an hour. – Rick James Aug 17 '17 at 21:55
  • @RickJames Yes. But i need to find a specific time, not a date. Only problem when it's next day. – Fortael Aug 18 '17 at 09:58

4 Answers4

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.

Neil McGuigan
  • 46,580
  • 12
  • 123
  • 152
  • 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. – Fortael Aug 10 '17 at 19:33
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?

Troels
  • 342
  • 3
  • 16
  • How it could be datetime? If i need to set time exactly for monday, not for 1 August. That's the problem exactly. – Fortael Aug 10 '17 at 13:33
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)
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • 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? – Fortael Aug 10 '17 at 13:44
  • 1
    dow - day of week. if you specify the timestamp - it will get the dow of week of that timestamp. – Vao Tsun Aug 10 '17 at 13:56
  • And now i realise that this is not helps with main problem - when time on next day. :( Not working @Vao Tsun – Fortael Aug 10 '17 at 17:29
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.

user7425525
  • 34
  • 5
  • 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 – Fortael Aug 10 '17 at 13:48
  • You have to update your shop start and end date-time in Data base. – user7425525 Aug 10 '17 at 14:16
  • during query? Could you provide and example? I can't find . anything useful. @Raushan – Fortael Aug 10 '17 at 17:32
  • 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 ) – user7425525 Aug 11 '17 at 05:57
  • 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. – Fortael Aug 11 '17 at 10:37
  • For editing: You can implement cron job/Scheduler to update database. – user7425525 Aug 11 '17 at 12:37