MySQL TIMESTAMP & PHP time() ?

[PHPFAN10]

Hello,

 

Currently i use php time() to store unix timestamp in MySQL MyISAM DB using varchar, if i changed the MySQL column to timestamp can i use php time() to insert php unix timestamp into the MySQL timestamp column? i don't want MySQL to automatically update it or anything on queries etc. I am not sure if it's rite but i have been reading that if i use mysql timstamp then everytime that row is access, updated, selected etc the timestamp column will be updated for that row; could anyone please verify this please?

 

If it auto updates can anyone recommend what i use for column type to store php time() in mysql MyISAM db? as i want to use php time() to insert unix timestamp as i was reading again that php time() unixtimestamp is not the same as MySQL TIMESTAMP. Somewhat confused on this.

 

Thanks

PHPFAN10

[Pikachu2000]

If it's a datetime data type, you can use mysql's NOW() to inert the current timestamp

 

EDIT: Hang on. DO you want to continue to use a UNIX timestamp format, or YYYY-MM-DD hh:mm:ss format?

[PHPFAN10]

Hi not sure what you mean but currently i just do time() and that inserts php unix timestamp in mysql db.

 

I don't mind using NOW() in mysql query but is it true a mysql TIMESTAMP column auto updates the timestamp for the row that has been for example selected/updated etc ?

 

PHPFAN10

 

UPDATE: FOR POST ABOVE - ReEdited***

 

When i retrieve the php unix timestamp from database i use php date() to convert it to what format i want it. I want to continue to use php unixtime stamp format and use php date() to convert it to format i want it in.

 

Thanks

PHPFAN10

[Pikachu2000]

See my edit above . . . Sorry.

[PHPFAN10]

Hi,

 

Thanks for replying, i update post above ^^

 

Thanks,

PHPLOVER10

[requinix]

A TIMESTAMP has the capability to automatically update. It doesn't have to. You can use it like a normal DATETIME field if you wish, but then you might as well just use a DATETIME to begin with.

 

                        | PHP         | MySQL            | MySQL data type
------------------------+-------------+------------------+--------------------------
Get a numeric timestamp | time()      | UNIX_TIMESTAMP() | INT or larger
Get a string timestamp  | date()      | NOW()            | DATE, DATETIME, TIMESTAMP
------------------------+-------------+------------------+--------------------------
Convert number->string  | date()      | FROM_UNIXTIME()
Convert string->number  | strtotime() | UNIX_TIMESTAMP()

[Pikachu2000]

OK. If you need to use a UNIX timestamp, you cannot use a datetime data type. It sounds like what you're doing now is probably the best thing you can do, with the exception of storing the UNIX timestamp in a VARCHAR field. It would be better to use an INT data type for that.

[PHPFAN10]

OK. If you need to use a UNIX timestamp, you cannot use a datetime data type. It sounds like what you're doing now is probably the best thing you can do, with the exception of storing the UNIX timestamp in a VARCHAR field. It would be better to use an INT data type for that.

 

Ok i see, i will keep it as it is and just change data type for that column.

 

Why $_SERVER['PHP_SELF'] is bad.

 

Thanks for that to in your sig as i never knew this . i changed all mine to:

 

<form method="post" action="<?php echo basename(__FILE__); ?>">

 

That to my knowledge and understanding will fix it; i hope so anyway.

 

Thanks

PHPFAN10

[DavidAM]

Just to be sure we are all on the same page ...

 

The TIMESTAMP datatype in mySql is different from the DATETIME datatype. The TIMESTAMP appears to be a UNIX timestamp "under the hood". It has the same range limitation as an unsigned time() value, and stores the UTC time. It can be set in the CREATE TABLE statement to automatically update on INSERT or UPDATE or both -- well, the first one in the table can be.

 

The DATETIME datatype does NOT have the unix range limitation, and seems to be based on storing the actual digits entered rather than a "real" time stamp.

 

The manual states:

The current session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns. Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval.

 

The current time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP() or values in DATE, TIME, or DATETIME columns. Nor are values in those data types stored in UTC; the time zone applies for them only when converting from TIMESTAMP values. If you want locale-specific arithmetic for DATE, TIME, or DATETIME values, convert them to UTC, perform the arithmetic, and then convert back.