How to choose Data Format for temporary unload from MySQL database?

Data Format for MySQL database

Problem

You need to store temporal data from MySQL Database but aren’t sure which is the most appropriate data type.

Solution

Choose the data type according to the characteristics of the information to be stored and how you need to use it.

To choose a temporal data type, consider questions such as these:

  • Do you need times only, dates only, or combined date and time values?
  • What range of values do you require?
  • Do you want automatic initialization of the column to the current date and time?

MySQL provides DATE and TIME data types for representing date and time values separately, and DATETIME and TIMESTAMP types for combined date-and-time values. These values have the following characteristics:

  • DATE values have CCYY-MM-DD format, where CC, YY, MM, and DD represent the century, year within century, month, and day parts of the date. The supported range for DATE

values is 1000-01-01 to 9999-12-31.

  • TIME values have hh:mm:ss format, where hh, mm, and ss are the hours, minutes, and seconds parts of the time. TIME values often can be thought of as time-of-day values, but MySQL actually treats them as elapsed time. Thus, they may be greater than 23:59:59 or even negative. (The actual range of a TIME column is -838:59:59 to 838:59:59.)
  • DATETIME and TIMESTAMP are combined date-and-time values in CCYY-MM-DD hh:mm:ss format.

    The DATETIME and TIMESTAMP data types are similar in many respects, but watch out for these differences:

    • DATETIME has a supported range of 1000-01-01 00:00:00 to 9999-12-31
    • 23:59:59, whereas TIMESTAMP values are valid only from the year 1970 partially through 2038.
    • TIMESTAMP and DATETIME have special auto-initialization and auto-update properties, but for DATETIME they are not available before MySQL 5.6.5.
    • When a client inserts a TIMESTAMP value, the server converts it from the time zone associated with the client session to UTC and stores the UTC value. When the client retrieves a TIMESTAMP value, the server performs the reverse operation to convert the UTC value back to the client session time zone. A client in a time zone different from the server can configure its session so that this conversion is appropriate for its own time zone.
  • Types that include a time part can have a fractional seconds part for subsecond resolution.

 

Вас заинтересует / Intresting for you:

Connecting with a MySQL databa...
Connecting with a MySQL databa... 2333 views Valerij Sun, 26 Aug 2018, 11:27:52
MySQL Database Overview
MySQL Database Overview 9736 views Antoniy Thu, 19 Sep 2019, 11:26:55
Determining characteristics of...
Determining characteristics of... 1335 views Valerij Sun, 26 Aug 2018, 12:52:37
MySQL Database optimization ba...
MySQL Database optimization ba... 2552 views Stepan Ushakov Fri, 05 Oct 2018, 17:15:28
Log in to comment