|Showing entries 1 to 10|
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
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.
Convert Datetime Value to Unix Timestamp[Read more...]
In a few scenarios, I prefer using the data-type TIMESTAMP of MySQL, with the flag ON UPDATE CURRENT_TIMESTAMP. This is much easier and even faster than using a trigger which sets a specified column to NOW() or CURRENT_DATE(). I love this feature, when it comes to set last modified flags of tables. Now, I encountered a problem with a tagging table by using the flag. Every tag in the table has besides the tag-name, also the aggregated number of relations and a timestamp of last update (or the insert). Everything is okay, until you want to update the aggregated column, when the numbers are wrong for any reason.
I’ve been thinking a lot about invalid and zero temporal values and how to detect them with MySQL date and time functions because mk-table-checksum has to handle “everything” correctly and efficiently. The requirements are complex because we have to take into account what MySQL allows to be stored verses what it allows to be used in certain operations and functions, how it sorts a mix of real and invalid temporal values for
MAX(), how to detect a temporal value as equivalent to zero, and how different MySQL versions might affect any of the aforementioned.
At base, the four guiding requirements are:
This article shows how to create a MySQL-plugin that can be used to create a function which can in turn be used in stored procedures. The function will produce an integer value representing the time (to the nearest usec).
I’m working on an article for conflict detection/resolution when using MySQL Cluster asynchronous replication which requires an integer column to store a timestamp for comparison purposes. In fact, it doesn’t actually need the timestamp to represent an absolute or even a relative point in time – all it cares about is that the if the function is called twice on 2 different hosts that the 2nd call will always result in a larger number than the 1st. Obviously, in a production environment the times on the 2 hosts would need to be kept in sync.
#include <mysql.h> #include[Read more...]
Once upon a time`stamp`, in a `data`base far far away, someone filed a bug named: `Microseconds precision is not retained by TIME, DATETIME, and TIMESTAMP field types.` – Bug Number 8523. This was the beginning of 2005, yet now that we are approaching the end of 2009, after 4.5 years, many (including myself) are still asking for this.
In fairness sake, MySQL have indeed supplied a way to retain milli and micro seconds in a decimal field `DECIMAL(17,3)`, and it is also queryable as if it were a timestamp BUT why isn’t it possible to store in a `DATETIME` or `TIMESTAMP` field? Why can’t we run a ’select now()’ or ’select curtime()’ etc and get a full timestamp including milli / micro seconds?
I have counted 37 different usernames asking for this feature, spanning from 15th Feb 2005 to recently. (list found below)[Read more...]
This is a post about
CURRENT_TIMESTAMP() functions in MySQL.
Firstly, note is that of these three, only
CURRENT_TIMESTAMP() is part of the SQL Standard.
NOW() happens to be an alias for
CURRENT_TIMESTAMP() in MySQL.
Secondly, note that replication does not work well with non-deterministic functions. And “hey, what time is it?” is non-deterministic. Ask it twice, with a second apart between asking, and both times you get different results (with at least second precision).
You can start to see the problem here….but there’s more…. (more…)
Since MySQL v4.1 timestamp and datetime data types are formatted “YYYY-MM-DD HH:MM:SS”. Prior to MySQL v4.1 the timestamp was formatted as YYYYMMDDHHMMSS” and datetime formatted as “YYYY-MM-DD HH:MM:SS”. Refer to MySQL Reference Manual for further details.
In MySQL you can use[Read more...]
Have you ever needed a random timestamp in MySQL? For example to create demo data programmatically? Here’s my solution:
SELECT FROM_UNIXTIME( FLOOR( UNIX_TIMESTAMP('2007-01-01') + RAND() * (UNIX_TIMESTAMP('2007-01-03')-UNIX_TIMESTAMP('2007-01-01')) ) ) as random_timestamp;
Or if you prefer a function:
CREATE FUNCTION random_timestamp (start TIMESTAMP, end TIMESTAMP) RETURNS TIMESTAMP NOT DETERMINISTIC RETURN FROM_UNIXTIME( FLOOR( UNIX_TIMESTAMP(start) + RAND() * (UNIX_TIMESTAMP(end)-UNIX_TIMESTAMP(start)) ) ); mysql> select random_timestamp('2007-10-01', NOW()); +---------------------------------------+ | random_timestamp('2007-10-01', NOW()) | +---------------------------------------+ | 2007-10-05 23:07:11 | +---------------------------------------+ 1 row in set (0.00 sec)
|Showing entries 1 to 10|