Postgres subtract minutes from timestamp

by

To subtract minutes from a PostgreSQL timestamp, you can use the INTERVAL keyword combined with the MINUTE field. Here is an example: 

<?php
  // Connect to the PostgreSQL database
  $conn = new PDO('pgsql:host=localhost;dbname=yourdatabase', 'yourusername', 'yourpassword');

  // Define the timestamp and the number of minutes to subtract
  $timestamp = '2020-01-15 10:30:00';
  $minutes = 15;

  // Subtract the minutes from the timestamp
  $sql = "SELECT timestamp '" . $timestamp . "' - INTERVAL '" . $minutes . " minute' AS new_timestamp;";
  $result = $conn->query($sql);
  $row = $result->fetch(PDO::FETCH_ASSOC);

  // Display the new timestamp
  echo $row['new_timestamp'];
?>

In the above example, we first establish a connection to the PostgreSQL database using PHP’s PDO extension. Make sure to replace localhost, yourdatabase, yourusername, and yourpassword with the appropriate values for your setup.

Next, we define the original timestamp (‘2020-01-15 10:30:00’) and the number of minutes to subtract (15). We then construct the SQL query using string concatenation, where we subtract the minutes from the timestamp using the INTERVAL keyword and the MINUTE field.

After executing the query and fetching the result, we use PHP’s echo statement to display the new timestamp.

The resulting output would be 2020-01-15 10:15:00, which is the original timestamp minus 15 minutes.

Leave a comment