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.
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.
The following is the query to format the date to YYYY-MM-DD. mysql> select str_to_date(LoginDate,'%d. %m.
DATETIME has a default of NULL unless defined with the NOT NULL attribute, in which case the default is 0.
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.
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With