Time Interval Better with Code or can I be lazy

Databases
1

Hi,

I come from a code background, but am making a concerted effort to not think programatically with my SQL and instead trying to be lazy.

I need to send email notifications based on certain days of the week and in-between a range of times. Here is the data on that:

[TABLE=“class: grid, width: 80%, align: center”]
[TR]
[TD]id[/TD]
[TD]schedule[/TD]
[TD]start_time[/TD]
[TD]end_time[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE]
[TR]
[TD]Daily - Morning[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE]
[TR]
[TD]00:00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE]
[TR]
[TD]00:00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE]
[TR]
[TD]Daily - Afternoon[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE]
[TR]
[TD]00:00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE]
[TR]
[TD]00:00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE]
[TR]
[TD]Daily - Evening[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE]
[TR]
[TD]00:00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE]
[TR]
[TD]00:00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE]
[TR]
[TD]Monday - Morning[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE]
[TR]
[TD]00:00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE]
[TR]
[TD]00:00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE]
[TR]
[TD]Monday - Afternoon[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE]
[TR]
[TD]00:00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE]
[TR]
[TD]00:00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE]
[TR]
[TD]Monday – Evening[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE]
[TR]
[TD]00:00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE]
[TR]
[TD]00:00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][TABLE]
[TR]
[TD]Wednesday - Morning[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE]
[TR]
[TD]00:00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE]
[TR]
[TD]00:00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][TABLE]
[TR]
[TD]Wednesday -Afternoon[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE]
[TR]
[TD]00:00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE]
[TR]
[TD]00:00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][TABLE]
[TR]
[TD]Wednesday - Evening[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE]
[TR]
[TD]00:00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE]
[TR]
[TD]00:00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][TABLE]
[TR]
[TD]Friday - Morning[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE]
[TR]
[TD]00:00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE]
[TR]
[TD]00:00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][TABLE]
[TR]
[TD]Friday - Afternoon[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE]
[TR]
[TD]00:00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE]
[TR]
[TD]00:00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][TABLE]
[TR]
[TD]Friday - Evening[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE]
[TR]
[TD]00:00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[TD][TABLE]
[TR]
[TD]00:00:00[/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[/TABLE]

I know how to do this programatically, however I am getting confused as the best way to do this using only SQL

Some questions:[LIST=1]
[]How best can I determine the day of the week?
[]CURRENT DATE only tells that it is today but not if it matches Monday, Wednesday, or Friday. Is there a way to determine if it is a Monday, Wednesday, or Friday?
[*]Time might be a little more straight forward as the intervals are the same for each day - I could use only a base time rather than start and end times and then use an Interval maybe or is the range the right way to do this?
[/LIST]Is this better not to do in SQL?

I don’t know if I have been clear enough to elicit your help, but if so it is appreciated.

Regards,
Steve

2

the table idea has definite potential to save lots of coding, but you have to design the data in such a way as to make the query trivial

can you explain how sending an email has a start time and end time?

also, how do you propose to initiate the php that will send the email? cron job or scheduled task or something like that?

unless you plan to run this job on a frequent basis every day i don’t understand how the time range is supposed to work

3

Hi Rudy,

The start and end times are basically to say the email process must be triggered between start and end times otherwise don’t send it. This is to deal with the imprecision of a scheduled times. I did not know the best way to deal with this?

The emails would be sent likely via a CRON job

I guess that I could get the Monday Wednesday and Friday on the CRON job but I am trying in ernest to learn SQL thoroughly and wanted to see how it might be accomplished via SQL.

Thanks,
Steve

4

okay, i get it

you’ll be fine with time ranges

use DAYOFWEEK(CURRENT_DATE) to get today’s day of week, where 1=sunday, 2=monday, etc.

this means your data table should use 1 through 7 as values in the dayofweek column (cases 4 through 12 in your examples)

for the daily stuff, cases 1 through 3, my advice is simply to code those as seven separate rows, one for each day – this makes the retrieval easier

the sql is simple, just use WHERE CURRENT_TIME BETWEEN starttimem AND enddtime, where starttimem and enddtime are TIME columns in your table

5

Awsome,

I would like you to know that I try hundreds of things and really try to research the right thing before posting. Sometimes, like with this, It becomes challenging to google terms you don’t know yet or to encapsulate the intent of the SQL when searching sitepoint, stackflow or google/yahoo/bing…

Thanks very much for your help. One day soon I will have to travel up the road from Burlington and take you out for a beer!

Regards,
Steve

6

Bring a frisbee, he’ll teach you frisbee golf! :slight_smile:

7

thanks for the kind words guys

:cool: