Convert TimeStamp into a more readable time

[garyjohnson]

I have a code that places the time into a database in the format Year-Month-Date Time. The time isnt in Pm or Am. I want to change the code from that into Month/Day/Year and the time in am or pm. Is this possible? Or should I be using a different method to placing the date into the database?

[diafol]

Keep the Y-m-d H:i:s format as you can sort on this. Month/Day/year is difficult to sort. You can run a plethora of php date functions to get the format you want when you need to display. Alternatively, you could write SQL to return the date time in the format required. I tend to shy away from formatting in SQL, but you may find it easier:

Assuming...
Y-m-d H:i:s -> m/d/Y h:i:s a

SELECT DATE_FORMAT(my_date_field, '%m/%d/%Y %r') FROM my_table;

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

[almostbob]

THE db is not supposed to be human readable,
it is meant for the machine to read
date stored as a timestamp, is smaller more efficient and faster, not so much for a few records, but it makes a large difference to use a four byte timestamp to a 25 byte text representation, in for example, 1 000 000 records
it is simpler to compare timestamps,
select \* from table where date > 1234567890 and date < 1234568300
than it is to
select \* from table date_format(my_date_field, '%m/%d/%Y %r')
by a time/processor factor several orders of magnitude

selected records are parsed to human readable only, using simple php date() format
for different users 2013/5/12 is fifth of December, or twelfth of May no consistency
a timestamp is unique, to the second, and can be parsed out to each user's chosen/or/regional-default settings, including am/pm
it would be more efficient to run a single table update, and continue using a timestamp.
SQL has a structure Now() just to populate timestamps on insert
the timestamp, is another expression of the php date() object, without formatting applied, sorry all, its a simple 4byte number

[diafol]

I agree with AB - as I said, try to avoid formatting in SQL. The timestamp is preferable to datetime. Ignoring my 'no formatting' statement, you could use this:

SELECT FROM_UNIXTIME(my_timestamp_field,'%m/%d/%Y %r') FROM my_table;

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime

Your only issues really are deciding on displayed timezones. A datetime will usually just give an artibary date part and a time part, without any timezone info. This shouldn't be an issue if your TZ is known.

However, a timestamp, as AB states, is valid for everywhere, as it's tied to UTC. Sorry if you already know this - just read this back and it sounds as though I've entered lecture mode. I'll switch off now.

[garyjohnson]

Okay I get what to do if i'm only selecting the time but what should the query be if I'm selecting multiple peices of data for example my current Query is

SELECT * FROM comments

So it selects everything how do I get the timestamp in the certain format when my query is written like so.

[diafol]

You should avoid * whenever possible. Specify the fields you need to retrieve. Then it's not a problem.

Otherwise, if specifying fields is not possible, for whatever reason, you can use php date and time functions to format...

$date = date('m/d/Y h:i:s a', $data['my_timestamp']);

You just feed in the timestamp from the DB resultset in your while or for loop.