Store the Day of the Week and Time

Store the day of the week and time?

Is there a way for me to store the the record for Tuesday and
Wednesday in one row or do should I have two records?

There are several ways to store multiple time ranges in a single row. @bma already provided a couple of them. That might be useful to save disk space with very simple time patterns. The clean, flexible and "normalized" approach is to store one row per time range.

What is the best way to store the day and time?

Use a timestamp (or timestamptz if multiple time zones may be involved). Pick an arbitrary "staging" week and just ignore the date part while using the day and time aspect of the timestamp. Simplest and fastest in my experience, and all date and time related sanity-checks are built-in automatically. I use a range starting with 1996-01-01 00:00 for several similar applications for two reasons:

• The first 7 days of the week coincide with the day of the month (for sun = 7).
• It's the most recent leap year (providing Feb. 29 for yearly patterns) at the same time.

Range type

Since you are actually dealing with time ranges (not just "day and time") I suggest to use the built-in range type tsrange (or tstzrange). A major advantage: you can use the arsenal of built-in Range Functions and Operators. Requires Postgres 9.2 or later.

For instance, you can have an exclusion constraint building on that (implemented internally by way of a fully functional GiST index that may provide additional benefit), to rule out overlapping time ranges. Consider this related answer for details:

• Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

For this particular exclusion constraint (no overlapping ranges per event), you need to include the integer column event_id in the constraint, so you need to install the additional module btree_gist. Install once per database with:

CREATE EXTENSION btree_gist;  -- once per db

Or you can have one simple CHECK constraint to restrict the allowed time period using the "range is contained by" operator <@.

Could look like this:

CREATE TABLE event (event_id serial PRIMARY KEY, ...);

CREATE TABLE schedule (
   event_id integer NOT NULL REFERENCES event(event_id)
                    ON DELETE CASCADE ON UPDATE CASCADE
 , t_range  tsrange
 , PRIMARY KEY (event_id, t_range)
 , CHECK (t_range <@ '[1996-01-01 00:00, 1996-01-09 00:00)')  -- restrict period
 , EXCLUDE USING gist (event_id WITH =, t_range WITH &&)      -- disallow overlap
);

For a weekly schedule use the first seven days, Mon-Sun, or whatever suits you. Monthly or yearly schedules in a similar fashion.

How to extract day of week, time, etc?

@CDub provided a module to deal with it on the Ruby end. I can't comment on that, but you can do everything in Postgres as well, with impeccable performance.

SELECT ts::time AS t_time           -- get the time (practically no cost)
SELECT EXTRACT(DOW FROM ts) AS dow  -- get day of week (very cheap)

Or in similar fashion for range types:

SELECT EXTRACT(DOW FROM lower(t_range)) AS dow_from  -- day of week lower bound
     , EXTRACT(DOW FROM upper(t_range)) AS dow_to    -- same for upper
     , lower(t_range)::time AS time_from             -- start time
     , upper(t_range)::time AS time_to               -- end time
FROM   schedule;

db<>fiddle here

_{Old sqliddle}

ISODOW instead of DOW for EXTRACT() returns 7 instead of 0 for sundays. There is a long list of what you can extract.

This related answer demonstrates how to use range type operator to compute a total duration for time ranges (last chapter):

• Calculate working hours between 2 dates in PostgreSQL

Best Way to store days of the week with Time Spans

I see no reason why a database view couldn't be used here with the help of a current indication field.

Assuming you had a Type 2 Dimension as such:

 UserID
,DayOfWeek
,StartWorkTime
,EndWorkTime
,EffectiveStartDate
,EffectiveEndDate
,Current -- Y/N Flags for being current or not current

You could effectively create a database view that pivoted StartWorkTime and EndWorkTime onto UserID and Current columns.

 CREATE VIEW [schema].[view_name] AS
 SELECT
    UserID
   ,[Current] -- Y = Yes / N = No
   ,MAX(CASE WHEN DayOfWeek = 2 THEN StartWorkTime  ELSE NULL END) AS Mon_StartWorkTime
   ,MAX(CASE WHEN DayOfWeek = 2 THEN EndWorkTime    ELSE NULL END) AS Mon_EndWorkTime 
   ,MAX(CASE WHEN DayOfWeek = 3 THEN StartWorkTime  ELSE NULL END) AS Tue_StartWorkTime
   ,MAX(CASE WHEN DayOfWeek = 3 THEN EndWorkTime    ELSE NULL END) AS Tue_EndWorkTime 
   ,MAX(CASE WHEN DayOfWeek = 4 THEN StartWorkTime  ELSE NULL END) AS Wed_StartWorkTime
   ,MAX(CASE WHEN DayOfWeek = 4 THEN EndWorkTime    ELSE NULL END) AS Wed_EndWorkTime 
   ,MAX(CASE WHEN DayOfWeek = 5 THEN StartWorkTime  ELSE NULL END) AS Thu_StartWorkTime
   ,MAX(CASE WHEN DayOfWeek = 5 THEN EndWorkTime    ELSE NULL END) AS Thu_EndWorkTime 
   ,MAX(CASE WHEN DayOfWeek = 6 THEN StartWorkTime  ELSE NULL END) AS Fri_StartWorkTime
   ,MAX(CASE WHEN DayOfWeek = 6 THEN EndWorkTime    ELSE NULL END) AS Fri_EndWorkTime 
 FROM [database].[schema].[table_name]
 WHERE [Current] = 'Y'
 GROUP BY
    UserID
   ,[Current]

Then your results would be for one employee working 0800 to 1600 some days and to 1200 other days as such:

UserID  Current Mon_StartWorkTime   Mon_EndWorkTime     Tue_StartWorkTime   Tue_EndWorkTime     Wed_StartWorkTime   Wed_EndWorkTime     Thu_StartWorkTime   Thu_EndWorkTime     Fri_StartWorkTime   Fri_EndWorkTime
1       Y       08:00:00.0000000    16:00:00.0000000    08:00:00.0000000    16:00:00.0000000    08:00:00.0000000    16:00:00.0000000    08:00:00.0000000    12:00:00.0000000    08:00:00.0000000    12:00:00.0000000

Storing day of week with time in database

There is no need to store this value in the database. Remember that you can always retrieve the day of week in SQL, like:

• using the DAYNAME command:

  SELECT DAYNAME(day) FROM tbl_days

• using the DAYFORMAT command:

  SELECT DATE_FORMAT(day, '%W');

More information at the MySQL Reference Manual for the Date and Time Functions

[EDIT]: You can declare the day column type as TIMESTAMP, then your INSERT query could be something like

INSERT INTO tbl_days (day) VALUES (NOW());

or have an explicit input such as:

SET @date = CONCAT($year,'-',$month,'-',$day,'-',$hour,'-',$minute,'-',$second);
INSERT INTO tbl_days (day) VALUES (STR_TO_DATE(@date,'%Y-%c-%e-%k-%i-%s'));

How to store week days + time in the database

You can use two php inbuilt functions

strtotime() and date()

For the database column kindly use DateTime

* Here is how you can do it *

$date_to_go_in_db = date('Y-m-d H:i:s',strtotime($string_date_from_date_picker));

To show date from db with days and Time such as 26th Monday june 2pm etc use this

date('Y-m-D g:i a', strtotime($dataobject->event_date_time_from_db));

What's the best way to store the days of the week an event takes place on in a relational database?

I would avoid the string option for the sense of purity: it adds an extra layer of encoding/decoding that you do not need. It may also mess you up in the case of internationalization.

Since the number of days in a week is 7, I would keep seven columns, perhaps boolean. This will also facilitate subsequent queries. This will also be useful if the tool is ever used in countries where the workweek starts on different days.

I would avoid the lookup because that would be over-normalization. Unless your set of lookup items is not obvious or could possibly change, it's overkill. In the case of days-of-the-week (unlike US states, for example), I would sleep soundly with the fixed set.

Considering the data domain, I don't think that a bitfield would achieve any significant space savings for you and would just make your code more complex.

Finally, a word of warning about the domain: a lot of schools do weird things with their schedules where they "swap days" to balance out an equal number of weekdays of each type over the semester despite holidays. I am not clear about your system, but perhaps the best approach would be to store a table of the actual dates in which the course is expected to take place. This way, if there are two Tuesdays in a week, the teacher can get paid for showing up twice, and the teacher for the Thursday that was canceled will not pay.

Storing and saving day-of-week/time - value pairs in Java

I took Panky1986's advice and used an EnumMap containing a TreeMap to store the hours and a nested TreeMap inside that to store the minutes which in turn stores the data.

/* This is the enumeration which tracks the days */
public enum DAY {WEEKDAY, SATURDAY, SUNDAY};

/* This is the map that stores the timings */
private EnumMap<DAY, TreeMap<Integer, TreeMap<Integer, String>>> mapDays;

Group object that contains store hours by day of week

You could look to open and close and add a new object. Otherwise change the days property.

var data = [{ dayOfWeek: "Monday", open: "8:00 AM", close: "5:00 PM" }, { dayOfWeek: "Tuesday", open: "8:00 AM", close: "5:00 PM" }, { dayOfWeek: "Wednesday", open: "8:00 AM", close: "6:00 PM" }, { dayOfWeek: "Thursday", open: "8:00 AM", close: "5:00 PM" }, { dayOfWeek: "Friday", open: "8:00 AM", close: "5:00 PM" }, { dayOfWeek: "Saturday", open: "8:00 AM", close: "4:00 PM" }],    result = data.reduce((r, { dayOfWeek, open, close }) => {        if (!r.length || r[r.length - 1].open !== open || r[r.length - 1].close !== close)            r.push({ days: dayOfWeek, open, close });        else            r[r.length - 1].days = r[r.length - 1].days.split('-').slice(0, 1).concat(dayOfWeek).join('-');                        return r;    }, []);
console.log(result);

.as-console-wrapper { max-height: 100% !important; top: 0; }

wrong$dt_start <- as.POSIXct(wrong$dt_start, format = "%d.%m.%Y %H:%M", tz = Sys.timezone())
wrong$opening_time <- format(as.POSIXct(wrong$dt_start), format = "%H:%M")
wrong$dt_start <- as.Date(wrong$dt_start)
wrong <- wrong[, c(1, 2, 5, 3, 4)]

wrong$dt_end <- as.POSIXct(wrong$dt_end, format = "%d.%m.%Y %H:%M", tz = Sys.timezone())
wrong$closing_time <- format(as.POSIXct(wrong$dt_end), format = "%H:%M")
wrong$dt_end <- as.Date(wrong$dt_end)
wrong <- wrong[, c(1, 2, 3, 4, 6, 5)]
wrong$weekday <- weekdays(wrong$dt_start)

new <- c("Friday", "Monday", "Saturday", "Sunday", "Thursday", "Tuesday", "Wednesday")
store$weekday <- new[match(store$weekday, store$weekday_code, nomatch = 0)]
store$weekday_code <- NULL
store <- store[, c(4, 1, 2, 3)]

library(dplyr)
wrong2 <- wrong %>%
  left_join(store, by = c("weekday")) %>%
  mutate(opening_time = coalesce(opening_time.y, opening_time.x)) %>%
  mutate(closing_time = coalesce(closing_time.y, closing_time.x)) %>%
  select(-opening_time.x, -closing_time.x, -closing_time.y, -opening_time.y)

   kod_dolg   dt_start     dt_end person_number   weekday workday_sign
1  50000690 2022-02-14 2022-02-14        976291    Monday            1
2       801 2022-02-14 2022-02-14        754105    Monday            1
3  50000690 2022-02-14 2022-02-14        867801    Monday            1
4       801 2022-02-15 2022-02-15            NA   Tuesday            1
5  50000690 2022-02-15 2022-02-15        867801   Tuesday            1
6       801 2022-02-15 2022-02-15            NA   Tuesday            1
7       801 2022-02-15 2022-02-15            NA   Tuesday            1
8  50000690 2022-02-15 2022-02-15        867801   Tuesday            1
9       801 2022-02-16 2022-02-16            NA Wednesday            1
10 50000690 2022-02-16 2022-02-16        976291 Wednesday            1
11 50000690 2022-02-16 2022-02-16        938541 Wednesday            1
12      801 2022-02-17 2022-02-17            NA  Thursday            1
13 50000690 2022-02-17 2022-02-17            NA  Thursday            1
14 50000690 2022-02-17 2022-02-17            NA  Thursday            1
15 50000690 2022-02-17 2022-02-17            NA  Thursday            1
16      801 2022-02-18 2022-02-18            NA    Friday            1
17 50000690 2022-02-18 2022-02-18            NA    Friday            1
18      801 2022-02-18 2022-02-18            NA    Friday            1
19 50000690 2022-02-18 2022-02-18            NA    Friday            1
20      801 2022-02-18 2022-02-18            NA    Friday            1
21 50000690 2022-02-18 2022-02-18            NA    Friday            1
22      801 2022-02-12 2022-02-12            NA  Saturday            1
23 50000690 2022-02-12 2022-02-12            NA  Saturday            1
24 50000690 2022-02-12 2022-02-12            NA  Saturday            1
25      801 2022-02-12 2022-02-12            NA  Saturday            1
26      801 2022-02-19 2022-02-19            NA  Saturday            1
27 50000690 2022-02-19 2022-02-19            NA  Saturday            1
28 50000690 2022-02-19 2022-02-19            NA  Saturday            1
29 50000690 2022-02-19 2022-02-19            NA  Saturday            1
30      801 2022-02-20 2022-02-20            NA    Sunday            1
31 50000690 2022-02-20 2022-02-20            NA    Sunday            1
32 50000690 2022-02-20 2022-02-20            NA    Sunday            1
33 50000690 2022-02-20 2022-02-20            NA    Sunday            1
34      801 2022-02-13 2022-02-13            NA    Sunday            1
35 50000690 2022-02-13 2022-02-13            NA    Sunday            1
36      801 2022-02-13 2022-02-13            NA    Sunday            1
37 50000690 2022-02-13 2022-02-13            NA    Sunday            1
   opening_time closing_time
1       9:00:00     18:00:00
2       9:00:00     18:00:00
3       9:00:00     18:00:00
4       9:00:00     18:00:00
5       9:00:00     18:00:00
6       9:00:00     18:00:00
7       9:00:00     18:00:00
8       9:00:00     18:00:00
9       9:00:00     18:00:00
10      9:00:00     18:00:00
11      9:00:00     18:00:00
12      9:00:00     18:00:00
13      9:00:00     18:00:00
14      9:00:00     18:00:00
15      9:00:00     18:00:00
16      9:00:00     18:00:00
17      9:00:00     18:00:00
18      9:00:00     18:00:00
19      9:00:00     18:00:00
20      9:00:00     18:00:00
21      9:00:00     18:00:00
22      9:00:00     17:00:00
23      9:00:00     17:00:00
24      9:00:00     17:00:00
25      9:00:00     17:00:00
26      9:00:00     17:00:00
27      9:00:00     17:00:00
28      9:00:00     17:00:00
29      9:00:00     17:00:00
30     10:00:00     16:00:00
31     10:00:00     16:00:00
32     10:00:00     16:00:00
33     10:00:00     16:00:00
34     10:00:00     16:00:00
35     10:00:00     16:00:00
36     10:00:00     16:00:00
37     10:00:00     16:00:00