Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 2

Displaying posts with tag: millisecond (reset)

Datetime & Timestamp manipulation / migration explained
+0 Vote Up -0Vote Down

Are you doing some datetime manipulation or maybe you are migrating from some database technology to MySQL or possibly using milliseconds?
Here is an example on how to go about it:

Say you have the following date: MAR 16 2008 09:12:51:893AM
SELECT DATE_FORMAT(STR_TO_DATE('MAR 16 2008 09:12:51:893AM','%M %d %Y %h:%i:%s:%f%p'),'%Y%m%d%k%i%s.%f'); --> 2008031691251.893000

What if its PM rather than AM
SELECT DATE_FORMAT(STR_TO_DATE('MAR 16 2008 09:12:51:893PM','%M %d %Y %h:%i:%s:%f%p'),'%Y%m%d%k%i%s.%f'); --> 20080316211251.893000

Ok so this is just simple string manipulation where:
%M is the month name
%d is day number
%Y is the year
%h is the hour
%i is the minute
%s is the second
%f is the microsecond
%p is the












  [Read more...]
Once upon a timestamp(milliseconds)….
+6 Vote Up -0Vote Down

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...]
Showing entries 1 to 2

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.