PHP MySQL date between

Question

The system is for phone contract comparison. Originally we could filter the result by length, given in months. However we've opted to storing this as a timestamp of the end date of contract in the database.

The original PHP was as follows:

$min = min($_POST["length"])-6;
$max = max($_POST["length"]);
$query[] = "length BETWEEN $min AND $max";

In summary, here's what we have at the moment:

In the database is stored the end-date of the contract (as a timestamp)
The post value contains how many months from now it should last (eg. 7-12 months would have a value of 7)

How can we change the PHP calculations to get the between values from the database?

Many thanks, Nick.

hjpotter92 · Accepted Answer · 2012-09-12 11:28:25Z

3

<?php
    $diff = 7;     // This may be set to $_POST['length']
    $max = date("Y-m-d");
    $min = date("Y-m-d", strtotime("$diff months ago"));
    $query[] = "DATE(`timestamp_field`) BETWEEN CAST($min AS DATE) AND CAST($max AS DATE)";
?>

edited Sep 12, 2012 at 11:28

answered Sep 12, 2012 at 11:04

hjpotter92

81.1k36 gold badges148 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Daniel Over a year ago

Is it not essential to cast the $min and $max dates to ensure the correct data type? DATE(timestamp_field) BETWEEN CAST($min AS DATE) AND CAST($max AS DATE)

Nick Price Over a year ago

@Daniel I'm unsure of the CAST function- what does it do?

hjpotter92 Over a year ago

@Daniel Ah, yes. I normally keep my date and time type columns separate. So, this was prebuilt code. :)

Daniel Over a year ago

@NickPrice; The CAST function makes sure that the value is of a certain data type. If you would compare $min $max without casting, the values would probably be treated as string, and not date. See link: dev.mysql.com/doc/refman/5.0/en/…

Collectives™ on Stack Overflow

PHP MySQL date between

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related