[RESOLVED] Date between query

[dcjones]

Hi all.

I know that is POST has appeared in the Nb's section but on the advice of a senior member I have reposted it to the database section.

I need to write a query that will look at date fields in my table that are stored as MySql "timestamp" type. i.e. "20070411000000"

The background is this: if a person is away on holiday between x and y dates, then echo their location address. So I have this:

Not on holiday:
house No = houseno
street = street
town = town
state = state
country = country
zip = zip

On holiday
house No = a_no
street = a_street
town = a_town
state = a_state
country = a_country
zip = a_zip

Then I turn to a query. A member in the Nb’s section suggested I use "SELECT CASE, WHEN NOW() BETWEEN” but we could not get it to work.

This is what we have so far for displaying dates in a friendly format in one section of my page.

SELECT * , DATE_FORMAT(departuredate, '%d/%m/%Y') as d_date,  DATE_FORMAT(accomarrivaldate, '%d/%m/%Y') as a_date,  DATE_FORMAT(accomdepartdate, '%d/%m/%Y') as ac_date,  DATE_FORMAT(profiledate, '%d/%m/%Y') as p_date
FROM members
WHERE member_id = colname

Included in the query how can I write something like this: an example:

If todays date is in between or = to accomarrivaldate (16-04-2007) and accomdepartdate (20-04-2007) then display “away” else display “home”

I am told that PHP will not do “between dates” so I need to look at MySQL. Can anyone help with trying to solve this?

I have never come across the "SELECT CASE, WHEN NOW() BETWEEN". Can you please help me through this. I know your time is valuable and it is much appreciated if you can. I am learning all the time.

[Installer]

dcjones wrote:

I am told that PHP will not do “between dates”...

I doubt many people would waste their time on a language as weak as that.

Fortunately, it's not true:

$curr_date = date('Y-m-d H:i:s');
while ($row = mysql_fetch_assoc($result)) {
    if (($curr_date >= $row['accomarrivaldate']) && ($curr_date <= $row['accomdepartdate'])) {
        echo 'Away<br />';
    } else {
        echo 'Home<br />';
    }
}

[Roger_Ramjet]

Some indication of your table structure would help, also mysql version.

First thing, you should not be using timestamp columns for these dates. Timestamp columns are datetime columns with special properties; they are used to automatically update and record when a record is inserted and/or updated. So change the dates to plain date column types: time of day is not necessary or useful information for holiday dates so datetime just gets in the way.

To select between dates just do this

"SELECT cols FROM table WHERE CURDATE() BETWEEN date1col AND date2col";

Probably a IF Statement would be more useful here, but it only applies from ver 5.0 so let us know what version you are using.

[Sxooter]

Roger Ramjet wrote:

Some indication of your table structure would help, also mysql version.

First thing, you should not be using timestamp columns for these dates. Timestamp columns are datetime columns with special properties; they are used to automatically update and record when a record is inserted and/or updated. So change the dates to plain date column types: time of day is not necessary or useful information for holiday dates so datetime just gets in the way.

Two points:
1: Only MySQL does this with timestamp, why? I don't know.
2: Only the first timestamp column gets updated automagically. If you have two, the second one won't get automagically updated on insert / update
3: You're absolutely right about the date versus timestamp thing. Oh wait, I said two points. Dammit! 🙂

Probably a IF Statement would be more useful here, but it only applies from ver 5.0 so let us know what version you are using.

Plus, IF is not a SQL standard, it's yet another MySQL extension that won't work on any other DB. Better to learn and use CASE, which is standard, and transportable.

[Roger_Ramjet]

Sxooter wrote:

Two points:
1: Only MySQL does this with timestamp, why? I don't know.

Beg to differ, mssql also has a timestamp mechanism which auto-updates when a row is changed. It is not a real datetime value however, just an internal counter that can be used to track if a row has been updated since you last read it.

Sxooter wrote:

2: Only the first timestamp column gets updated automagically. If you have two, the second one won't get automagically updated on insert / update

Beginning with MySQL 4.1.2, you have more flexible control over when automatic TIMESTAMP initialization and updating occur and which column should have those behaviors:

*      For one TIMESTAMP column in a table, you can assign the current timestamp as the default value and the auto-update value. It is possible to have the current timestamp be the default value for initializing the column, for the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

*      You can specify which TIMESTAMP column to automatically initialize or update to the current date and time. This need not be the first TIMESTAMP column.

This means that you can have 2 timestamp columns: one to record when the row was created and another to record when it was last updated. (you can of course have as many as you want, but why bother)

Sxooter wrote:

Plus, IF is not a SQL standard, it's yet another MySQL extension that won't work on any other DB. Better to learn and use CASE, which is standard, and transportable.

Except if you are using stored procedures in any db that supports them.

But true, learning to use CASE would be a good idea in any case 😉

[Sxooter]

Wow, so the guys who coded MSSQL server are as incapable of reading the SQL specs as the guys who wrote MySQL...

That does not reassure me about MSSQL very much. 🙂

Glad to see that MySQL has updated the whole timestamp thing. So, you can turn off the autoupdate behavior period? Or are you stuck having one that has to be auto-updated if you start using the SQL standard type timestamp the way it was intended?

[Roger_Ramjet]

Sxooter wrote:

Wow, so the guys who coded MSSQL server are as incapable of reading the SQL specs as the guys who wrote MySQL...

That does not reassure me about MSSQL very much. 🙂

SQL SERVER 2005:
The Transact-SQL timestamp data type is different from the timestamp data type defined in the SQL-2003 standard. The SQL-2003 timestamp data type is equivalent to the Transact-SQL datetime data type.

Don't see why you are so surprised, it is by MickeySoft after all. :rolleyes:

Sxooter wrote:

Glad to see that MySQL has updated the whole timestamp thing. So, you can turn off the autoupdate behavior period? Or are you stuck having one that has to be auto-updated if you start using the SQL standard type timestamp the way it was intended?

4.1.2 on
"In other words, you can use the current timestamp for both the initial value and the auto-update value, or either one, or neither. (For example, you can specify ON UPDATE to get auto-update without also having the column auto-initialized.)"

Pretty good flexibility I'd say. Trouble is the default behaviour is the non-standard behaviour which makes it very dodgy for novices. But then I suppose they did it for novices. :queasy:

[Roger_Ramjet]

PS in sqlsever you use a trigger on a datetime column to get the required timestamp behaviour. Oh well, at least it has triggers.

[dcjones]

Hi all, and thanks fr your replies.

The versions
PHP Version 4.4.4
MySQL Server version: 4.0.27
MySQL client version: 4.0.25

I have chaged all the "timestamp" fileds to "date£ type. The code I am working with is:

$curr_date = date('d-m-Y');
if (($curr_date >= $row_owners['a_date']) && ($curr_date <= $row_owners['ac_date'])) {
echo 'Away';
$c_address = 1;
} 
else
{
echo 'Home';
$c_address = 0;
}

and for my SQL query

SELECT * , DATE_FORMAT(departuredate, '%d/%m/%Y') as d_date,  

DATE_FORMAT(accomarival, '%d/%m/%Y') as a_date,  

DATE_FORMAT(accomdepartdate, '%d/%m/%Y') as ac_date,  

DATE_FORMAT(profiledate, '%d/%m/%Y') as p_date
FROM amember_baggagemembers
WHERE member_id = colname 

This I think outputs "accomarival" as "a_date" and "accomdepartdate" as " ac_date"with day,month and year format.

All I am trying to do is:

If todays date is is equal to "a_date" and "ac_date" or in between" print "Away" if not print "Home".

Any help would be great.

[Roger_Ramjet]

Basically you can do this in sql using CASE

"SELECT CASE WHEN CURDATE() BETWEEN accomarrivaldate AND accomdepartdate 
THEN 'away' ELSE 'home' END AS loc, etc"

Now all you have to do in php is test the value of loc.

[dcjones]

Hi, and thanks agina for all your time and input.

I now having the following query, but it is giving an error.

SELECT *, CASE WHEN CURDATE() BETWEEN accomarrivaldate AND accomdepartdate THEN 'Away' ELSE 'Home' END AS loc,   DATE_FORMAT(departuredate, '%d/%m/%Y') as d_date,    DATE_FORMAT(accomarival, '%d/%m/%Y') as a_date,    DATE_FORMAT(accomdepartdate, '%d/%m/%Y') as ac_date,    DATE_FORMAT(profiledate, '%d/%m/%Y') as p_date,
FROM amember_baggagemembers
WHERE member_id = colname 

No atter what I do it give an error.

What do you advise.

[Roger_Ramjet]

Help if you posted the error message

[dcjones]

Hi Sorry.

the error is

MySQL Error#: 1064

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM amember_baggagemembers WHERE member_id = -1' at line 1

[Roger_Ramjet]

comma before FROM

[dcjones]

HI Roger

Many thanks for your help with this.

It is now working as requiered. Thanks