Mysql Timestamps

Anon

You guys/gals are great help.

I seem to be stuck here, I am saving posts with a 14 digit myswl timestamp. This part works fine, I have found many references to doing this and people using date() and time() together to get it to read correctly.

I either get some whacked date or I get todays date. I cant seem to get it to work correctly. I just want a $date="Oct 6 23" and a $time="03:23:34 pm"

Your help is appreciated as always!

Anon

Retrieve the timestamp from mysql using unix_timestamp().

Also, you should never set a timestamp to any value other than null or itself. so

update table set lastupdate = lastupdate, othercol='val' is valid and so is
update table set othercol = 'val'
if you don't want timestamp to update.

If you really don't want timestamp to update ever, you should use a datetime instead of a timestamp.

Anon

I use an INT field to save timestamps (using unix/system time). So to save (say) todays date in a news item post I would use something like

$rightnow=mktime();

and insert that INT variable into the database table.

The only time I convert the timestamp to human readable form is for screen output.

echo date("M d Y",$rightnow);

will give you "Mmm d yyyy" output, and

echo date("h i s a",$rightnow); will give
"hh mm ss am|pm" output for the same timestamp

You can do really easy date math using INT field by simply adding or subtracting hours/days/weeks to the stored value.

All you need to remember is that there's 86400 or so seconds in one day ...

hope this helps (i found dates confusing until I hit on this one!!)

Anon

update table set othercol = 'val'
if you don't want timestamp to update.

That sounds backwards. Setting a timestamp to NULL is supposed to
update it with the current time; assigning the same value back to it
is how you're supposed to keep it from being updated.

Anon

You're bypassing all the features of mysql, though. The great thing about a mysql timestamp column is that it's updated automatically with the current time, so you don't have to do extra PHP statements to find the current time and such...

Also, mysql has a date_format() function to withdraw that date in any format you want, similar to date() in PHP.

So, if you want to get "Oct 6 23" and "03:23:34 pm", you'd use the following:

SELECT date_format(timestamp_column,'%b %c %d') as date, date_format(timestamp_column,'%r') as time from your_table;

Then, once you extract your data out into a row, use

$date = $row["date"];
$time = $row["time"];

There are also addition and subtraction functions for dates, read the manual.
http://www.mysql.com/documentation/mysql/bychapter/manual_toc.html#Date_and_time_functions

---JH