What is the best way to store user registration date in the mysql database ? should I use now()? unix_timestamp()?

Question

I was using this code to do this but it doesn’t seem to work! besides when I ask the best way is thinking of the possible difficulties I will have in the future as I am beginner should probably exist some good practice to store this type of information in the database but do not know yet.

timecreated timestamp not null default unix_timestamp()

Answer 1

It is necessary to better understand the types and their characteristics to be able to answer this:

DATA TYPE:

• now() returns a type DATETIME;
• unix_timestamp() a guy INT;

SIZE:

• DATETIME by default takes 5 bytes + seconds or 8 bytes depending on the version of mysql
• INT by default takes 4 bytes

TIME ZONE

• DATETIME stores the date and the Timezone
• TIMESTAMP or INT when converted to TIMESTAMP (for example using unix_timestamp()), "converts using the current UTC time zone configured on the server, and then converts again using the UTC time zone configured on the server"(1), i.e., does not preserve the Timezone.

RANGE OF VALUES

• DATETIME according to the documentation (1), values between '1000-01-01 00:00:00.000000' and '9999-12-31 23:59:59.999999'
• TIMESTAMP or INT when converted to TIMESTAMP according to the documentation (1), values between '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'

SUMMARY

• DATETIME takes up more space, but preserves the date with original UTC, does not convert to read/write, used for any date;
• TIMESTAMP, occupies less space, implicitly always converts from/to current UTC, has a crease more restricted dates, used for dates in this range, and if UTC is not an issue, for example to know if a record is newer/older than the current date, etc.

(1) of documentation of mysql :

"Mysql converts TIMESTAMP values from the Current time zone to UTC for Storage, and back from UTC to the Current time zone for Retrieval. (This does not occur for other types such as DATETIME.)"