Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How datetime values are stored in mysql?

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.

like image 466
Mangu Singh Rajpurohit Avatar asked Apr 29 '16 11:04

Mangu Singh Rajpurohit


People also ask

How are dates represented in MySQL?

MySQL recognizes DATE values in these formats: As a string in either ' YYYY-MM-DD ' or ' YY-MM-DD ' format. A “relaxed” syntax is permitted: Any punctuation character may be used as the delimiter between date parts. For example, '2012-12-31' , '2012/12/31' , '2012^12^31' , and '2012@12@31' are equivalent.

How can get date in dd mm yyyy format in MySQL?

The following is the query to format the date to YYYY-MM-DD. mysql> select str_to_date(LoginDate,'%d. %m.

What is the default value for datetime in MySQL?

DATETIME has a default of NULL unless defined with the NOT NULL attribute, in which case the default is 0.

What is data type of date in MySQL?

SQL Date Data Types MySQL comes with the following data types for storing a date or a date/time value in the database: DATE - format YYYY-MM-DD. DATETIME - format: YYYY-MM-DD HH:MI:SS. TIMESTAMP - format: YYYY-MM-DD HH:MI:SS. YEAR - format YYYY or YY.


2 Answers

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
like image 80
shaoyihe Avatar answered Oct 08 '22 17:10

shaoyihe


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.

like image 29
Drew Avatar answered Oct 08 '22 17:10

Drew