How datetime values are stored in mysql?

Question

I am new to db world. I am sending a datetime value such as '2016-04-27 09:00:00' from java program, for getting it saved into mysql database. My question is that How will this value be saved into datetime type of field in mysql db table. I mean, whether It'll be stored as string as I have passed or mysql will parse the string, and constructs UTC timestamp out of it and store that number into db. Moreover, Will this stored value be retrieved as it is from java program, irrespective of different timezone settings of mysql server and java server ?

Sorry for such a silly question, but It's hurting me a lot and didn't get simple and less confusing answer, while searching over internet.

Hallah

This blog post might answer it: How Is a Date Stored? – Standridge

Native types are stored in native formats. – Outpatient

can you explain more about "It's hurting me a lot and ,,," – Conjugate

Is this a Java problem or a MySQL problem? – Brittbritta

Answer 1

From the 10.9 Date and Time Data Type Representation :

DATETIME encoding for nonfractional part:

 1 bit  sign           (1= non-negative, 0= negative)
17 bits year*13+month  (year 0-9999, month 0-12)
 5 bits day            (0-31)
 5 bits hour           (0-23)
 6 bits minute         (0-59)
 6 bits second         (0-59)
---------------------------
40 bits = 5 bytes

Ehrenburg

changed from 5.6.4 dev.mysql.com/doc/refman/5.7/en/storage-requirements.html, mysql server source code here github.com/mysql/mysql-server/blob/… – Judgeship

Answer 2

The datetime datatype for mysql 5.6.4 and after is saved as described in the manual page Date and Time Data Type Representation. In short, it is a 5-byte format including fractional seconds and is divorced of timezone information.

Test table

create table dtTest
(
    id int auto_increment primary key,
    dt datetime not null
);

insert dtTest (dt) values ('2016-04-27 09:00:00');

If one needed to confirm this, one could jump into a hex editor of the saved file and verify the big-endian format of that datetime.

show variables like '%datadir%';
C:\ProgramData\MySQL\MySQL Server 5.6\Data\

Traverse into folders and the db table (dtTest.ibd)

There are system and session variables for timezone:

show variables like '%system_time_zone%';
Eastern Daylight Time

show variables like '%time_zone%';
SYSTEM

Let me be clear that I am completely clueless of the importance of these. Hopefully a peer can add clarity.

SELECT @@global.time_zone, @@session.time_zone;
SYSTEM    SYSTEM

Test a reset to something different:

SET SESSION time_zone = '+10:00';
select * from dtTest;

Note, the above setting change does not affect output of data. It is still raw and unrelated to timezone.

SET SESSION time_zone = 'America/Chicago';

for the above to work, see:

http://dev.mysql.com/doc/refman/5.6/en/mysql-tzinfo-to-sql.html

Otherwise, without that timezone support loaded, one would need to perform something like:

SET SESSION time_zone = '+10:00';

SELECT @@global.time_zone, @@session.time_zone;
SYSTEM      +10:00

setting session back to my default:

SET SESSION time_zone = 'SYSTEM';

---

SELECT COUNT(*) FROM mysql.time_zone_name;

My count is 0 because I have not loaded the timezone support table yet

SELECT CONVERT_TZ('2007-03-11 3:00:00','US/Eastern','US/Central');
-- so this is NULL for me. No support table loaded yet on my system.

SELECT CONVERT_TZ('2007-03-11 3:00:00','-4:00','-7:00');
2007-03-11 00:00:00 -- means that 3am NYC is midnight in San Fran

So the above is not NULL (less friendly to read UTC time offsets. Hard-coded).

SELECT id, CONVERT_TZ(dt,'-4:00','-7:00') from dtTest;  -- dt was my column name
1   2016-04-27 06:00:00

The above select statement take the 9am value in the db, can converts it from NYC time to San Fran time.

That is the best I've got for you. Hopefully someone can fill in the missing details of timezone support.

Conjugate

Answer 3

It uses a brilliant technique to store and retrieve the Date efficiently. This method allows to store of a compact version of a date which requires fewer bits to store the Date.

---

It uses a total of 8 bytes to store Date and Time, let's break it down.

Bits	Field	Value
1	sign	(used when on disk)
17	year*13+month	(year 0-9999, month 0-12)
5	day	(0-31)
5	hour	(0-23)
6	minute	(0-59)
6	second	(0-59)
24	microseconds	(0-999999)

Total: 64 bits = 8 bytes

Let's focus on the Date part here, how it holds the date in a compact format & Why multiply with 13.

The reason for choosing 13 as the multiplier is that it allows for a convenient representation of leap years. When a year is divisible by 4, it is considered a leap year and has 366 days instead of the usual 365. In the MySQL DateTime format, leap years are represented by adding 1 to the month value.

Let's Understand it by an example

Let's say we want to store the date (YYYY - MM): 2023-12

as per the calculation = 2023 × 13 = 26299

then (+) the month = 26299 + 12 = 26311

So, the compact form of 2023-12 is 26311

How do we extract the year and month from the compact form?

for YEAR = 26311 / 13 = 2023

for MONTH = 26311 % 13 = 12

That's how it works. for the Day there is another field of which is of 5 bits. Check GitHub Code

Thanks

Andalusite

Answer 4

tl;dr

• Use TIMESTAMP column in MySQL to record a moment, a specific point on the timeline. Matching type in Java is OffsetDateTime class.
• Use DATETIME column in MySQL to record merely a date with time-of-day without any concept of offset-from-UTC nor time zone. Matching type in Java is LocalDateTime class.

If in doubt or unclear, avoid DATETIME, choosing TIMESTAMP instead in MySQL.

Purpose	Standard SQL	MySQL	Java (JDBC)
Record a moment, a point on the timeline. Represent a date with time-of-day in context of offset or zone.	TIMESTAMP WITH TIME ZONE	TIMESTAMP	OffsetDateTime
Mere date with time-of-day. No offset, no time zone.	TIMESTAMP WITHOUT TIME ZONE	DATETIME	LocalDateTime

Details

Other Answers give interesting details about the internal details of the MySQL implementation of DATETIME data type. While interesting, such implementation details are subject to change, and are largely irrelevant to us as Java app developers.

What matters is the contract laid out in documentation. To quote the 8.2 doc:

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'.

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.499999', …

Note that DATETIME differs from TIMESTAMP in its ignorance of offset-from-UTC or time zone. To quote the doc again:

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.) …

So the DATETIME type in MySQL is akin to the SQL standard type of TIMESTAMP WITHOUT TIME ZONE, representing merely a date & time.

You said:

I am sending a datetime value such as '2016-04-27 09:00:00' from java program

In a Java app, you should be using java.time date-time objects rather than mere text to exchange date-time values with the database. If you have a textual value such as "2016-04-27 09:00:00", parse it as a LocalDateTime. The LocalDateTime class represents a date with time-of-day but lacks the context of an offset or zone.

To parse such a string, substitute the SPACE in the middle with a T to comply with the ISO 8601 standard.

String input = "2016-04-27 09:00:00".replace( " " , "T" ) ;
LocalDateTime ldt = LocalDateTime.parse( input ) ;

Write to database in a MySQL column of the type DATETIME.

myPreparedStatement.setObject( … , ldt ) ;

Retrieval:

LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;

You said:

whether It'll be stored as string as I have passed or mysql will parse the string

No, no serious database engine stores date-time values as text. Each database has its own internal binary representation of date-time values.

If you pass text as input to the database, the text is parsed into an internal binary format before being stored. Likewise, upon retrieval the binary formatted data retrieved is converted back to text by a console-access app or as a Java object by a JDBC driver.

constructs UTC timestamp out of it and store that number into db

No, no, no! Be very careful here. The DATETIME type you mentioned in your Question has no concept of offset or time zone. DATETIME values in MySQL do not represent a moment, are not a point on the timeline. A DATETIME value is just a date with a time-of-day, nothing more. So if the value is noon on January 23 of 2024, we have no way to know if that is noon in Tokyo, noon in Toulouse, or noon in Toledo Ohio — three very differ9ent moments, several hours apart. A DATETIME value in MySQL is inherently ambigous!

If your goal is to track a moment, a specific point on the timeline, you must use a data type akin to the SQL standard type of TIMESTAMP WITH TIME ZONE. In MySQL 8, that would be the MySQL type TIMESTAMP. In Java, the matching type defined in JDBC is java.time.OffsetDateTime.

OffsetDateTime odt = … ;
myPreparedStatement.setObject( … , odt ) ;

Retrieval:

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;

When you write to a MySQL column of type TIMESTAMP, any given offset or time zone is used to adjust the moment to an offset from UTC of zero hours-minutes-seconds.

You asked:

Will this stored value be retrieved as it is from java program, irrespective of different timezone settings of mysql server and java server ?

When you retrieve a MySQL DATETIME value, you get just a date and a time-of-day, without offset or zone. When using a JDBC driver, you get a LocalDateTime as that is the class for a date with time-of-day that lacks the context of an offset or zone.

So time zone settings in your OS, your database engine, your database session, and your JVM are all irrelevant. A date and time go into the database, and you get back the exact same date and time upon retrieval.

In contrast, with a MySQL TIMESTAMP column, when writing to the database any submitted offset or zone is used to determine a value in UTC (zero offset), then stored in UTC. On retrieval, you get back a UTC value.

Beware: Some access tools and middleware may inject a time zone or offset — after retrieving a UTC value from the database such an anti-feature may dynamically apply some default offset or zone before handing over the resulting value. No JDBC driver does this that I know of, but it definitely happens in many database access tools (unfortunately).

Generally in business apps, use TIMESTAMP type in MySQL as we generally care about specific moments. Use DATETIME type in MySQL only if you are really clear on the context, behavior, and purpose. When in doubt, use TIMESTAMP.

Viewer