Record Time Stamp

[Zergman]

I've been tasked with having our record entry page now do a time stamp along with the date.

 

Origionally, the date column in the database was set to DATE, but since changed to DATETIME.  I did set the entry page to echo as follows

echo(date('c')

But this will snapshot the datetime when the page loads, not when the record is submitted.

 

How would I go about recording the date and time when the record was submitted rather when the page loads?  Sometimes, its quite a few min between record entries so the time is not accurate.

[Zergman]

K, changed the column to TIMESTAMP and it seems to be working as I wanted.  Recording the time the entry went into the database.

 

Just curious though if I will run into any problems with this if I decide to modify the record.  Will the timestamp format change to the updated time instead of when it was submitted?

[Zergman]

One problem has popped up.

 

Since data in the database looks like this

2008-11-17 14:14:23

My normal variable of

$date = date ('Y-m-d');

now doesn't work.  It will only work on entries that have 00:00:00 for the time.  ANything record with an actual time, won't come up in my query.

 

Here's my query

SELECT * FROM `data` WHERE tdate = '$date' ORDER BY '$slevel11'

 

Suggests to make it search for any record for the day now that the time is included?

[premiso]

It will not work cause you are doing an = to the date. It has to match exactly, meaning you need the hours, minutes and seconds display like they are in the DB with your date value.

 

$date = date ('Y-m-d h:m:s'); // not sure on the hours minutes / seconds look at the date function via php.net for correct usage.

[Zergman]

The more I look into it, im not sure its a actual date() issue but perhaps a mysql situation.

 

Reading the manual on Date and Time functions for mysql, im guessing its due to the TIMESTAMP column type.

 

CURDATE() should work, but doesn't appear to be.

[premiso]

The more I look into it, im not sure its a actual date() issue but perhaps a mysql situation.

 

Reading the manual on Date and Time functions for mysql, im guessing its due to the TIMESTAMP column type.

 

CURDATE() should work, but doesn't appear to be.

 

The timestamp column type is just bad news. If it was me, depending on how many records are currently there, I would do a dump of all the rows via php and convert that time column to a unix timestamp value and put it into a text file as an INSERT statement to the database. Then I would dump all the current data in the table and change the row type to INT(11).

 

But that is just me and it does depend on how many rows you currently have to tell whether that is probable or not.

[Zergman]

Perfect, thats the advise I was looking for!

 

So let me see if I got this plan down.

 

1) Change the column to int

2) Set my variable on the entry page to enter a unix timestamp

 

Would that work?  Cause I can imagine it would be easy to search for what I want by using wildcards... I think

[premiso]

Yep, however if you do not re-create the table using data converted to the timestamp you may lose all the dates prior when you changed this. (Assuming this is on a production server and not a development/test server).

 

[Zergman]

Aye, im testing everything out on a different server, but the production server has been around for a while and has a lot of data on it.

 

I don't think im ready or gutsey enough to attempt it on the production server.

 

Here's on thing that puzzles me.

This query works.  It pulls all records for the current month.

SELECT * FROM `data` WHERE MONTH(`tdate`) = '$month' AND YEAR(`tdate`) = '$year'

 

Month and year variable

$month = date ('m');
$year = date ('Y');

 

What could I do to add to this to pull from current date as well leaving the column as either TIMESTAMP or DATETIME?

 

Tried using DAYOFMONTH() but that didn't seem to work.