SEO

#1 (**permalink**) 03-10-2008, 04:55 PM

Hi,

I am new to MySql and I am not really sure what the equivalent of a
"cron job" would be but I need a way for the DB to automatically
update a column in my table based on certain conditions.

The table has a date, starttime and endtime column. In the same table
is a column called Status. If the Date and starttime has not yet
happened, the status is Upcoming. If the Date is the same as the
current Date AND we are between the Start and Stop times in the same
table, the Status is Active. If the current Date is after the date and
endtime in the column, the status is Closed. So:

IF currentdate < posted date
And current time < start time
then
status = 'upcoming'
End If

If currentdate > posted date
And current time > End time Then
status = 'closed'
End IF

If current date = posted date
AND current time < endtime AND
current time > starttime then
status = active
End IF

I think the above basically sums up the psuedo code.
What do I need to do to automate this code in my DB?

Thanks.

#2 (**permalink**) 03-10-2008, 04:55 PM

On Mon, 10 Mar 2008 07:03:15 -0700 (PDT), pbd22 wrote:
> Hi,
>
> I am new to MySql and I am not really sure what the equivalent of a
> "cron job" would be but I need a way for the DB to automatically
> update a column in my table based on certain conditions.
>
> The table has a date, starttime and endtime column. In the same table
> is a column called Status. If the Date and starttime has not yet
> happened, the status is Upcoming. If the Date is the same as the
> current Date AND we are between the Start and Stop times in the same
> table, the Status is Active. If the current Date is after the date and
> endtime in the column, the status is Closed. So:
>
> IF currentdate < posted date
> And current time < start time
> then
> status = 'upcoming'
> End If
>
> If currentdate > posted date
> And current time > End time Then
> status = 'closed'
> End IF
>
> If current date = posted date
> AND current time < endtime AND
> current time > starttime then
> status = active
> End IF
>
> I think the above basically sums up the psuedo code.
> What do I need to do to automate this code in my DB?

Normally, I'd think that this information would be derived at query-time
in exactly that kind of manner, but this could happen very simply with a
set of UPDATE queries submitted by cron itself. MySQL itself doesn't
have that kind of functionality.

--
I got told by a friend's ex-girlfriend that she could tell I was
a Linux geek from the way I *walked*.
-- Skud

#3 (**permalink**) 03-17-2008, 06:13 AM

>I am new to MySql and I am not really sure what the equivalent of a
>"cron job" would be but I need a way for the DB to automatically
>update a column in my table based on certain conditions.
>
>The table has a date, starttime and endtime column. In the same table
>is a column called Status. If the Date and starttime has not yet
>happened, the status is Upcoming. If the Date is the same as the
>current Date AND we are between the Start and Stop times in the same
>table, the Status is Active. If the current Date is after the date and
>endtime in the column, the status is Closed. So:
>
>IF currentdate < posted date
>And current time < start time
>then
>status = 'upcoming'
>End If
>
>If currentdate > posted date
>And current time > End time Then
>status = 'closed'
>End IF
>
>If current date = posted date
>AND current time < endtime AND
>current time > starttime then
>status = active
>End IF

It seems to me that you could do this with a view, and not
store the status field at all. Or you could compute it on
those queries that need it.

Also, your code seems to have gaps in it. For example, what
is the status if:
1. currentdate > posted date AND current time < end time
2. current date = posted date AND current time < starttime
3. current date = posted date AND current time > end time

You might find it works better to use a datetime. Then, it seems
you've got three cases:

current datetime < start datetime (upcoming)
current datetime >= start datetime AND current datetime < end datetime (active)
current datetime >= end datetime (closed)

and things will work if a start/end time crosses midnight.

#4 (**permalink**) 03-17-2008, 06:13 AM

Thanks both.
I soved this by parsing on the server as suggested.

Thank you again

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

MySql Cron Job For Date Manipulation