Introduction Dealing with time zones is always challenging. As a rule of thumb, it’s much easier if all date/time values are stored in the UTC format, and, if necessary, dealing with time zone conversations in the UI only. This article is going to demonstrate how you can accomplish this task with JDBC and the awesome … Continue reading How to store date, time, and timestamps in UTC time zone with JDBC and Hibernate →
A question which would come sometimes to mind when starting with
MySQL is whether I should use DATETIME or TIMESTAMP data type
since both appear to store same date and time component.
Similarities between datetime and timestamp:
1. Values contain both date and time parts.
2. Format of retrieval and display is "YYYY-MM-DD
HH:MM:SS".
3. Can include a trailing fractional seconds part in up to
microseconds (6 digits) precision.
4. With the fractional part included, the format for these values
is "YYYY-MM-DD HH:MM:SS[.fraction]".
5. Both the TIMESTAMP and (as of MySQL 5.6.5) DATETIME offer
automatic initialization and updating to the current date and
time.
But both differ in some ways as mentioned below:
…
It's usually a good practice to have two columns called
date_created and date_updated in every table. One
can always use it in application and it helps in debugging too as
to when a record was created and last updated in various
circumstances and contexts.
This responsibility can be given to MySQL to automatically assign
current time values to these columns.
In MySQL 5.6 onwards, this can be done by setting the data type
of the columns to be either date time or timestamp and creating
date_created column with NOT NULL DEFAULT
CURRENT_TIMESTAMP schema and date_updated column
with NOT NULL DEFAULT '0000-00-00 00:00:00' as schema with
attribute ON UPDATE CURRENT_TIMESTAMP.
…
In this post, we’ll discuss how MySQL 5.7 handles the old temporal types during an upgrade.
MySQL changed the temporal types in MySQL 5.6.4, and it introduced a new feature: microseconds resolution in the TIME, TIMESTAMP and DATETIME types. Now these parameters can be set down to microsecond granularity. Obviously, this means format changes, but why is this important?
Are they converted automatically to the new format?
If we had tables in MySQL 5.5 that used TIME, TIMESTAMP or DATETIME are these fields are going to be converted to the new format when upgrading to 5.6? The answer is “NO.” Even if we …
[Read more]
I was a bit wrong in my previous post. MySQL 5.6 does allow you
to supply a fsp with CURRENT_TIMESTAMP (thanks Roy).
mysql> SELECT CURRENT_TIMESTAMP,CURRENT_TIMESTAMP(6);
+---------------------+----------------------------+
| CURRENT_TIMESTAMP | CURRENT_TIMESTAMP(6) |
+---------------------+----------------------------+
| 2013-10-27 10:38:59 | 2013-10-27 10:38:59.182530 |
+---------------------+----------------------------+
1 row in set (0.00 sec)
It however feels a bit weird to me as the CURRENT_TIMESTAMP is
often used without () and doesn't look like a function. So when I
tried to use a CURRENT_TIMESTAMP with a fsp of 6 it was not
behaving how I expected it to be:
mysql> CREATE TABLE t1 (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP(6));[Read more]
ERROR 1067 (42000): Invalid …
MySQL 5.6 includes support for microsecode timestamp resolution, which is a
great new feature.
To get the current timestamp in MySQL 5.5 you could use NOW(),
SYSDATE() or CURRENT_TIMESTAMP.
mysql_5.5> SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP;
+---------------------+---------------------+---------------------+
| NOW() | SYSDATE() | CURRENT_TIMESTAMP |
+---------------------+---------------------+---------------------+
| 2013-10-26 15:46:24 | 2013-10-26 15:46:24 | 2013-10-26 15:46:24 |
+---------------------+---------------------+---------------------+
1 row in set (0.01 sec)
If we run the same statement in MySQL 5.6 the output is the same.
This is great for compatibility, but what if we want those
microsecond timestamps?
…[Read more]
MySQL 5.6 introduces a new feature – microseconds resolution in some temporal types. As of 5.6.4 TIME, TIMESTAMP and DATETIME can have a fractional part. To create a field with subseconds you can specify precision in brackets: TIME(3), DATETIME(6) etc.
Obviously, the new feature requires the format change. All three types may now have a tail with a fractional part. The tail may be one, two or three bytes long and can store up to six digits after the point.
The non-fractional part has changed too. Thus, DATETIME uses only five bytes comparing to eight in previous versions.
As of revision 79 the …
[Read more]
There are great features in MySQL 5.6. But not only that. We also
tried to correct some old behaviors and limitations which, over
the years, have shown to irritate our Community. The behavior of
TIMESTAMP columns is one of them.
My colleague Martin Hansson did most of the work and summarized
it well in his blog. Thanks to him, since MySQL 5.6.5, it's
possible to declare more than one TIMESTAMP column with the
DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP
attributes. And it's possible to have DATETIME columns with such
attributes. Two limitations lifted!
But that is not the end of the story. TIMESTAMP was still
special. Unlike other datatypes, if not declared with the NULL or
NOT NULL attributes, it would automatically get NOT NULL. And the
first TIMESTAMP column of the table would automatically …
In the yet to be released MySQL 5.6.6 DMR, there has been a change to the restriction of just one TIMESTAMP column with the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP syntax. It is now possible for any TIMESTAMP to have either column defintion.
More information at http://dev.mysql.com/doc/refman/5.6/en/news-5-6-5.html
Background Knowledge
I will explain how to convert a DATETIME (data type) value in Microsoft SQL Server to Unix timestamp and how to convert Unix timestamp to DATETIME. A Unix timestamp is a integer value of seconds since January 1, 1970 at midnight. For further explanation of Unix timestamps refer to Wikiepedia, UnixTimestamp.com or http://unixtimesta.mp/.
Note: This solution only work on dates prior to 2038-01-19 at 3:14:08 AM, where the delta in seconds exceeds the limit of the INT data type (integer is used as the result of DATEDIFF). See source for further details as I have not verified a solution to this problem.
Solutions
Convert Datetime Value to Unix Timestamp (today)