best way to create a timestamp?

[monkeytooth]

Well im mixed results on google, and the actual php manaul.. so yet another question by me to those who seem to know what they are talkin about, when i am stuck without a clue..

 

I am going to be using a timestamp like system in certain areas of one of my sites..

So I have a multipart question..

 

What is the best type of method to timestamp? I need to be able to break the time stamp down into seconds minutes hours days weeks months years.. That said how would I do that with the best type of method that will hopefully be mentioned here today..

 

These timestamps will be stored in mysql.. i belive 4.1.. if that helps any or makes a diffrence..

[akitchin]

you need to distinguish first between unix timestamp and timestamp, as these are different in MySQL.  unix timestamp is the number of seconds since the UNIX epoch, whereas MySQL timestamp is a DATETIME column which updates anytime the row is accessed or affected by a query.

 

your best bet is to use a DATETIME type of column in MySQL, as this allows you to use its date/time functions (such as DATE_FORMAT()) to break it into the chunks you're after.  the manual has more details on that, and i believe it's section 8 (Functions and Operators).

 

TIMESTAMP itself often has unexpected results for users who don't understand how it works.  make sure that if you're going to use it, you've read the documentation and understand how it will behave.

[genericnumber1]

I personally prefer to store all my timestamps as unix timestamps because they are the ones most easily suited to use in php. If you do decide to save them as mysql timestamps though, it is fairly trivial to change between the two with php's strtotime() and date() functions and this will allow you to use mysql's timestamp functions.

[Mchl]

See Date/Time functions

strtotime() returns Unix timestamp, which is what you need to pass to mySQL (although for some time now, you can also pass timestamp as string) ( http://dev.mysql.com/doc/refman/5.0/en/datetime.html )

[Mchl]

MySQL timestamp is a DATETIME column

 

Is not. As of MySQL 4.1 MySQL TIMESTAMP is stored as a UNIX Time. It's just the display that's same as for DATETIME

 

http://dev.mysql.com/doc/refman/4.1/en/datetime.html

[akitchin]

regardless of how it is physically stored, it is, for all intents and purposes, the same as a DATETIME column; when given a straight number value, it's still not a UNIX timestamp, it's just the DATETIME format without the dashes, spaces and colons.

[Mchl]

I seem to have mixed things up... I could swear I had been inserting UNIX timestamps into TIMESTAMP columns

 

Maybe I was using this?

[akitchin]

probably - i'm not a huge fan of them allowing that format, but i suppose if you learn the lesson once, it'll stick with you.  i mostly try to use entirely MySQL-side formatting for my date/time stuff to avoid crossing over and dealing with the confusion.

[monkeytooth]

Well what Im trying to do over all is on one side have a time stamp that does update itself one way or another upon use of that particular table

 

This is pretty much a "Last Seen" concept that I want to do. 1, to keep tabs on users.. I want to insert 2 timestamps or timestamp like objects into 2 diffrent columns on the same row. One is going to be the sign up date, one is going to be last login...

 

Last Login will be the constant updating timestamp.. and Sign Up with just be there.. This way I can through conversion break it down into minutes hours days weeks whatever. And see how long they have been around vs. How long since there last login. Grant it im toying with ideas for other stuff that may involve timestamps.. but if i can find out which method is best for what I want to do with whats mentioned here.. then I can figure the other ideas im toying with based on that.

 

I just dont know what would be a better method, and after finding that out.. I probally wouldnt know the best way to make it work.. I don't often use timestamps so im sorry if i come across no0bish.. but all help would be greatly appreciated..

[akitchin]

any of the DATETIME columns will work if you just update it to NOW() - MySQL takes the correct value of the current time for the given field type.  the danger to a TIMESTAMP column is that it will update anytime you edit that row.  you're better off just making it DATETIME and updating it to NOW() whenever the user logs in.

 

other functions that would be helpful, in the context of what you want to do, are:

 

DATE_FORMAT()
TO_DAYS()
DATE_DIFF()

 

MySQL has plenty of functions built-in for you to use.  just have a look in the manual, Mchl linked you earlier in the thread.

[Mchl]

the danger to a TIMESTAMP column is that it will update anytime you edit that row.

 

Only if it is defined to ON UPDATE CURRENT_TIMESTAMP (the first timestamp column in a table is defined as such by default)

 

In a CREATE TABLE statement, the first TIMESTAMP column can be declared in any of the following ways:

• With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses, the column has the current timestamp for its default value, and is automatically updated.
  
• With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
  
• With a DEFAULT CURRENT_TIMESTAMP clause and no ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated.
  
• With no DEFAULT clause and with an ON UPDATE CURRENT_TIMESTAMP clause, the column has a default of 0 and is automatically updated.
  
• With a constant DEFAULT value, the column has the given default and is not automatically initialized to the current timestamp. If the column also has an ON UPDATE CURRENT_TIMESTAMP clause, it is automatically updated; otherwise, it has a constant default and is not automatically updated.
  

[akitchin]

granted, but in that case, where's the utility of specifying TIMESTAMP over DATETIME?

[Mchl]

4 bytes?

 

TIMESTAMP is 4 bytes.

DATETIME is 8 bytes.

[akitchin]

4 bytes?

 

haha, touche my good sir.  touche.

[monkeytooth]

Alright Im lost lol, reading over the debate dunno which way to swing.. or what I should tempt.. anyone have something as an example of how they would do it that maybe I can look at that to learn from?

[Mchl]

To save current timestamp to mySQL use:

INSERT INTO table (timestamp) VALUES (NOW()); 

Will work no matter if the column is DATETIME or TIMESTAMP

 

To break timestamp into array of hours,minute,seconds etc. :strptime()

[akitchin]

or use DATE_FORMAT() when querying for the date, if you'd prefer to do it on the MySQL side.

[eldorik]

I use time() in php to make a unix timestamp

 

I store them in a INT column