CODEWITHSUNDEEP
mysql
Sachin
Sachin

Reputation: 1698

What does zeros mean in mysql date time?

In my MySQL database table, I have one column for datetime field. I'm using jQuery datetime picker to insert date and time. The value from a textbox, for example is inserted in the database as:

2018-09-01 00:00:00

Date is ok. But I'm wondering what does these zeros in time format mean? Is this time ok? If I will do some date time based calculation in PHP, then will it make the calculation wrong?

Upvotes: 0

Views: 299

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28844

Quoting from MySQL Documentation:

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

So, the time stored in the MySQL is in 24-hour format. So, basically 00:00:00 represents Midnight (12:00:00 AM)

Additionaly, MySQL allows storing microseconds as well. Again quoting from the documentation:

A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. In particular, any fractional part in a value inserted into a DATETIME or TIMESTAMP column is stored rather than discarded. With the fractional part included, the format for these values is 'YYYY-MM-DD HH:MM:SS[.fraction]', the range for DATETIME values is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999', and the range for TIMESTAMP values is '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'. The fractional part should always be separated from the rest of the time by a decimal point; no other fractional seconds delimiter is recognized.

Upvotes: 2

Related Questions