Create valid timestamp: YYYYMMDDHHMMSS from decreasing anoth

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
dimitris
Forum Contributor
Posts: 110
Joined: Wed Jan 14, 2004 3:47 am
Location: Athens, Greece

Create valid timestamp: YYYYMMDDHHMMSS from decreasing anoth

Post by dimitris »

I want to find the previous day from e.g. 20060601121530 (-> 2006-06-01 12:15:30).
How is it possible with PHP;
This timestamp is auto generated in mysql in a TIMESTAMP field using the NOW() function in mysql.

Any ideas are welcome!
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

The DATE_ADD() function - ADDDATE() is a synonmym - is used to add a particular date or time interval to a give date or time.

Code: Select all

DATE_ADD(datetime, INTERVAL expression datetimetype)
For example, to find a date 14 days after the 13th July, 2003, you can use:

Code: Select all

mysql> SELECT DATE_ADD('2003-07-13', INTERVAL 14 DAY);
+-----------------------------------------+
| DATE_ADD('2003-07-13', INTERVAL 14 DAY) |
+-----------------------------------------+
| 2003-07-27                              |
+-----------------------------------------+

You can also use a negative expression to subtract datetimes. To find the datetime 22 hours and 14 minutes before the the 13th July, 2003, 1 minute and 1 second past 1, you can use:

Code: Select all

mysql> SELECT DATE_ADD('2003-07-13 01:01:01', INTERVAL -'22:14' HOUR_MINUTE);
+----------------------------------------------------------------+
| DATE_ADD('2003-07-13 01:01:01', INTERVAL -'22:14' HOUR_MINUTE) |
+----------------------------------------------------------------+
| 2003-07-13 00:39:01                                            |
+----------------------------------------------------------------+
User avatar
dimitris
Forum Contributor
Posts: 110
Joined: Wed Jan 14, 2004 3:47 am
Location: Athens, Greece

Post by dimitris »

Thanx!

And how is it possible to get with PHP the returning value?

I mean i can use:

Code: Select all

$sql="SELECT DATE_ADD( '20060601', INTERVAL -1 DAY ) ";
$result=mysql_query($sql);
$row=@mysql_fetch_array($result);
but how i call this returned value? (e.g. echo $row['RETURNED_DATE'])
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

dimitris wrote:Thanx!

And how is it possible to get with PHP the returning value?

I mean i can use:

Code: Select all

$sql="SELECT DATE_ADD( '20060601', INTERVAL -1 DAY ) ";
$result=mysql_query($sql);
$row=@mysql_fetch_array($result);
but how i call this returned value? (e.g. echo $row['RETURNED_DATE'])
Use an ALIAS....

Code: Select all

$result = mysql_query("SELECT difficult(expression) AS easy FROM foo");
$row = mysql_fetch_assoc($result);
echo $row['easy'];
Post Reply