MySQL DATETIME Math

ThaSpY

I would like to subtract 5 minutes from the variable containing:
2003-09-09 16:17:30
So that it will become:
2003-09-09 16:17:25
Thanks

csn

Check out the date_sub function.

SELECT DATE_SUB("2003-09-09 16:17:30", INTERVAL 5 MINUTE)

ThaSpY

Sorry, I'm new to this function. Could you please explain it in a bit more detail? Thanks!

LordShryku

RTFM
http://www.mysql.com/doc/en/Date_and_time_functions.html

ThaSpY

So are you giving me a MySQL query here? eg:

$result = mysql_query("SELECT DATE_SUB(lastactive, INTERVAL 5 MINUTE) FROM users WHERE id = '$id'");

am i doing this right?
i have a field in my database called "lastactive" as i've included above. also, is there an easier way to do this without doing the math while taking it out of the database?
thanks.

superwormy

You coudl also use the PHP function strtotime() to convert it to seconds, them subtract ( 60 seconds * 5 minutes ) seconds from the resulting value.

ThaSpY

Could you please give an example?

csn

Straight from the manual:

int mktime ( [int hour [, int minute [, int second [, int month [, int day [, int year [, int is_dst]]]]]]])

http://www.php.net/manual/en/function.mktime.php

mktime() is useful for doing date arithmetic and validation, as it will automatically calculate the correct value for out-of-range input. For example, each of the following lines produces the string "Jan-01-1998". Example 1. mktime() example

<?php
echo date ("M-d-Y", mktime (0,0,0,12,32,1997));
echo date ("M-d-Y", mktime (0,0,0,13,1,1997));
echo date ("M-d-Y", mktime (0,0,0,1,1,1998));
echo date ("M-d-Y", mktime (0,0,0,1,1,98));
?>

ThaSpY

i'd prefer a concrete example, ive already read that, but it doesnt explain too much.

superwormy

$now = "2003-09-15 20:00:00";
$now_seconds = strtotime ($now);
$now_seconds_minus_5_minutes = $now_seconds - ( 60 * 5 );
$new_date = date ("Y-m-d h:i:s", $now_seconds_minus_5_minutes);