MySQL timestamp with php

[amalosoul]

MySQL stores a timestamp like this YYYYMMDDHHMMSS, is there a php function that allows me to read from a MySQL timestamp field only the day? Or do I have to write it myself:)?

Thank you!

[roopurt18]

I prefer to let the database handle things such as this.

SELECT DATE_FORMAT([i]timestamp[/i], '%e') AS DayFromTS FROM [i]table[/i] WHERE 1

[i]timestamp[/i] is the name of the column containing the timestamp value, '%e' is the format to return (Day of the month, numeric (0..31)), DayFromTS is the name that it will return it as.

MySQL is a very, very powerful langugae.  Before you go off writing bits of PHP code to accomplish some tasks, I would look around in the MySQL manual at the [b]Functions and Operators[/b] found here:
http://dev.mysql.com/doc/refman/4.1/en/functions.html

[bqallover]

MySQL timestamps are formatted differently depending on the server version and mode, so you have to bear that in mind.

You could use the SQL function DAY(), as in

[code]
SELECT DAY(timestamp_field) FROM table
[/code]

or if you need something for PHP and you're certain that's the format of your field (and not YYYY-MM-DD HH:MM:SS ?) you could just do

[code]
$day = substr( $timestamp, 6, 2 ); // where format is YYYYMMDDHHMMSS
[/code]

[roopurt18]

I thought there was a better way to do it than with DATE_FORMAT but for some reason I missed both DAY and DAYOFMONTH when glancing at the MySQL documentation.

I'd use one of those!