Viewed   94 times

In certain situations I want to add 1 day to the value of my DATETIME formatted variable:

$start_date = date('Y-m-d H:i:s', strtotime("{$_GET['start_hours']}:{$_GET['start_minutes']} {$_GET['start_ampm']}"));

What is the best way to do this?

 Answers

4

If you want to do this in PHP:

// replace time() with the time stamp you want to add one day to
$startDate = time();
date('Y-m-d H:i:s', strtotime('+1 day', $startDate));

If you want to add the date in MySQL:

-- replace CURRENT_DATE with the date you want to add one day to
SELECT DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY);
Wednesday, September 28, 2022
4

If your dates are already in MySQL you will want to do the comparison in the query because:

  1. MySQL has proper DATE types.
  2. MySQL has indexes for comparison.
  3. MySQL performs comparisons much faster than PHP.
  4. If you filter your data in the query then less, or no time is spent transferring superfluous data back to the application.

Below is the most efficient form. If there is an index on the date column it will be used.

SELECT *
FROM table
WHERE date > DATE_SUB(NOW(), INTERVAL 15 MINUTE)

Docs: DATE_SUB()

If you need to do it in PHP:

$now = time();
$target = strtotime($date_from_db);
$diff = $now - $target;
if ( $diff > 900 ) {
  // something
}

or, more succinctly:

if( time() - strtotime($date_from_db) > 900 ) {
  // something
}
Wednesday, October 5, 2022
 
1

Rough half-pseudo code:

$records = /* SELECT * FROM `records` ORDER BY `entry_time` */;

$date = null;
foreach ($records as $record) {
    $currentDate = date('Ymd', $record['entry_time']);
    if ($currentDate != $date) {
        printf('<h1>%s</h1>', date('Y-m-d', $record['entry_time']));
    }
    $date = $currentDate;

    echo date('H:i', $record['entry_time']);
}
Monday, September 19, 2022
 
4
<?php
$stop_date = '2009-09-30 20:24:00';
echo 'date before day adding: ' . $stop_date; 
$stop_date = date('Y-m-d H:i:s', strtotime($stop_date . ' +1 day'));
echo 'date after adding 1 day: ' . $stop_date;
?>

For PHP 5.2.0+, you may also do as follows:

$stop_date = new DateTime('2009-09-30 20:24:00');
echo 'date before day adding: ' . $stop_date->format('Y-m-d H:i:s'); 
$stop_date->modify('+1 day');
echo 'date after adding 1 day: ' . $stop_date->format('Y-m-d H:i:s');
Friday, December 23, 2022
4

In SQL Server 2008 you can use the datetimeoffset data type.

SELECT [Result] = CONVERT(datetimeoffset, '2005-08-08T00:01:00+01:00', 127)

Output:

Result
----------------------------------
2005-08-08 00:01:00.0000000 +01:00

In SQL Server 2005 and earlier you can compute the UTC date and offset:

SELECT [LocalDate], [OffsetMinutes], [UtcDate]
FROM
(
    SELECT [IsoDate] = '2007-08-08T00:01:00+01:00'
) A
OUTER APPLY
(
    SELECT [LocalDate] = CONVERT(datetime, LEFT([IsoDate], 19), 126)
    , [OffsetMinutes] =
        CASE SUBSTRING([IsoDate], 20, 1)
            WHEN '+' THEN +1
            WHEN '-' THEN -1
        END
        * DATEDIFF(minute, 0,
            CAST(SUBSTRING([IsoDate], 21, 5) + ':00' AS datetime))
    WHERE [IsoDate] LIKE '____-__-__T__:__:__[+-]__:__'
) B
OUTER APPLY
(
    SELECT [UtcDate] = DATEADD(minute, -[OffsetMinutes], [LocalDate])
) C

Output:

LocalDate               OffsetMinutes UtcDate
----------------------- ------------- -----------------------
2007-08-08 00:01:00.000 60            2007-08-07 23:01:00.000
Friday, November 25, 2022
 
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :