0

I'm storing opening hours in a postgresql database in an jsonb field. My object looks like this:

{
"mon": [
  {
    "id": "aaaa-bbbb-cccc-dddd",
    "startTime": "08:00",
    "endTime": "12:00"
  },
  {
    "id": "eeee-ffff-gggg-hhhh",
    "startTime": "13:00",
    "endTime": "18:30"
   }
],
"tue": [
  {
    "id": "hhhh-iiii-jjjj-kkkk",
    "startTime": "08:00",
    "endTime": "12:00"
  }
],
...
}

So on mondays the store is open 08:00 (08AM) to 12:00 (12PM) and 13:00 (01PM) to 18:30 (06.30PM). On tuesdays, it's only open from 13:00 to 18:30.

With JavaScript, I create a nice table on my website to display the opening hours. This all works fine, but I want to add something more: I want to be able to show the user if the store is currently open or not when he visits the website.

First of all, I'm storing the timezone of the opening hours in a separate column (opening_hours_timezone). For me, thats Europe/Brussels. I want to be able to show the opening of the store. I live in Belgium: when I visit the website on monday at 16:00 (04PM), it should say 'OPEN'. At that point, it's 10AM in NYC, and it should also say 'open' when I visit the website from New York. When it's 04PM in New York, it's 10PM in Belgium so it should say 'closed'.

I'm familiar with momentJS, is this an option I could use? And should I do this check server-side (when I fetch the results from the database), or should I fetch the whole opening hours object and check it client-side? Personally, I think client-side is the best option.

Can someone point me in the right direction? Or is there a better option to store opening hours in a database?

Edit: I'm going to check the open status server-side (nodeJS), but I still don't have a clue to use the object I have and to match it with the current time.

Sam Leurs
  • 1,592
  • 1
  • 19
  • 48
  • 1
    Might make more sense to check this on the server-side - because then it doesn’t matter much what time it is in New York, because you are using the _server_ time to base this decision on to begin with. – CBroe Nov 30 '20 at 13:12
  • mmmh , my server timezone is UTC. Is it an option to fetch the whole object from the database and do my check in my nodeJS code? I think I'm going to try that! Thank you for your answer! – Sam Leurs Nov 30 '20 at 13:15
  • 1
    "Or is there a better option to store opening hours in a database?" -- Well, just a big JSON doesn't really use the DBMS' capabilities at all. You could have just used a plain file for that as well. [Here](https://stackoverflow.com/questions/64958816/how-to-store-a-store-opening-hours-in-an-sql-database/64959804#64959804) is a recent question on how to store opening hours in a relational database. It would be rather easy to query if a venue is open or not at a given time in such a model. Although that question doesn't include timezone handling, it could be a starting point for you. – sticky bit Nov 30 '20 at 13:19

0 Answers0