What Is the Data Type for Unix_Timestamp (Mysql)

What is the data type for unix_timestamp (MySQL)?

the type is integer like :

int(11) 

is good for indexing and conditions like > < =

How to store UNIX timestamps with MySQL

A Unix timestamp is a large integer (the number of seconds since 1970-01-01 00:00:00 UTC), so INT(11) is the correct datatype.

Unfortunately, I don't think there's any way to specify a default that will insert the current timestamp. You'll need to call UNIX_TIMESTAMP() explicitly when inserting, and use that. Function calls aren't allowed in DEFAULT specifications.

Store date as unix timestamp or TIMESTAMP data type in MySQL?

Usually it does not matter whether you use TIMESTAMP or DATETIME datatype.

• In older versions, TIMESTAMP was 4 bytes and DATETIME was 8.
• Think of DATETIME as a picture of a clock; think of TIMESTAMP as an instant in time, worldwide. That is, if you connect to the same database, but from a different timezone, a DATETIME will look the the same, but a TIMESTAMP will be adjusted for timezone.
• NOW(), SELECTing into PHP, etc, are compatible with both.
• Both are externally seen as a string, such as '2015-04-25 17:09:01'.
• Since TIMESTAMP is stored as a 32-bit integer (but you don't see that), it is limited to ~1970-2038.
• Since DATETIME is clock time, there will be a missing/extra hour twice a year if you switch to/from daylight savings time.

Yes, you could use UNIX_TIMESTAMP() and have an INT UNSIGNED, but wouldn't it be better to see '2015-...'? (That would be 4 bytes.)

Should I use the datetime or timestamp data type in MySQL?

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native DATETIME format. You can do calculations within MySQL that way
("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

MySQL timestamp field does not accept unix_timestamp() result

Try removing the UNIX_TIMESTAMP and STR_TO_DATE function.

TIMESTAMP fields work with the yyyy-mm-dd hh:mm:ss format (same as DATETIME).

How to store unix timestamp as int with default and on update?

If you want to automatically get an integer when you select the column, you need to make it an int (or int unsigned) type. You can set its default with default (unix_timestamp()) (the extra parentheses are needed when not using one of the historically allowed default values). And you will need to add a trigger to set it on update.

But I suggest you not do that; just use a timestamp type. You just make future trouble for yourself by not using the type designed to store timestamps.

Difference between UNIX_TIMESTAMP and NOW() in MySQL

The function NOW() generates a formatted date-time string, determined by the time zone of your MySQL server.

However, it would be better to store times using UNIX_TIMESTAMP(), which is expressed in GMT. Doing so makes it easier to format it according to the country of a visitor (e.g. using JavaScript).

If you still want to use DATETIME columns, you can store times using UTC_TIMESTAMP() (it formats a date like NOW() but expresses it in UTC); it should more or less work the same in all other aspects.

---

Related Topics