Question 20202 in Mysql Question posted in
The official MySQL documentation can be found here.

comparing data fields to local time in PHP – Mysql

I am pulling date from local server:

$year = date("Y");
$day = date("d");
$month = date("m");

In my MySQL database, I have three fields, dateday datemonth and dateyear on a record.

Format of database values are 26,6,1971.

I want to select only records with dates that are equal to today or not past yet.

Second question, can I trick date() to give dates in eastern US time when the server is in the UK?

Thanks!!

Tags:

2

Answers


  1. 0

    You don’t "trick date()", you use an API that is more useful.

    $year  = 1971;
$day   = 26;
$month = 6;

$src_tz = new DateTimezone("Europe/London");
$dst_tz = new DateTimezone("America/New_York");

$d = new DateTime('', $src_tz);
$d->setDate($year, $month, $day);
$d->setTime(0, 0, 0);
$d->setTimezone($dst_tz);

var_dump($d->format('c'));

    Output:

    string(25) "1971-06-25T19:00:00-04:00"

    Ref: https://www.php.net/manual/en/class.datetime.php

    On a broader note you can simplify your life by storing and computing all dates in a single timezone, ideally UTC as it has no DST or other nonsense attached. Timezones then become solely the the domain of the presentation layer.

    Login or Signup to reply.
  2. 0

    To search for table rows that are equal to or greater than today, you can use the following query:

    SELECT * FROM your_table
WHERE DATE(CONCAT(dateyear, '-', datemonth, '-', dateday)) >= CURDATE()

    This is what it would look like if you concatenate PHP variables into a PHP string to create the query.

    $query = "SELECT * FROM your_table
WHERE DATE(CONCAT(".$dateyear.", '-', ".$datemonth.", '-', ".$dateday.")) >= CURDATE()";

    For your second question, you can set the timezone in your PHP script with this command.

    date_default_timezone_set('America/New_York')

    Also, you can see more information about timezone here
    List of Supported Timezones
    date_default_timezone_set information

    Final script

    date_default_timezone_set('America/New_York');
$year = date("Y");
$day = date("d");
$month = date("m");

$query = "SELECT * FROM your_table
WHERE DATE(CONCAT(".$dateyear.", '-', ".$datemonth.", '-', ".$dateday.")) >= CURDATE()";
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search