Databases :: MySql :: Date time data type |
|||
By: PHP newbee  |
Date: 24/09/2004 00:00:00  |
Points: 50 | Status: Answered Quality : Excellent |
|
Dear all, In MySQL, I want to store a datetime value. My datetime value include: year, month, day, hour, minute, seconds, milisecond. How can I store this value because currently datetime data type of MySQL does not support milisecond?? Thank you and best regards, |
|||
| By: VGR | Date: 24/09/2004 16:50:00 | |
Type : Answer |
| simple : 1) either drop the millisecond value 8-) 2) goto 1) :D Even UNIX_TIMESTAMP function returns number of SECONDS since epoch0 kidding 90% :D 3) store yourself the value in a char(18) as YYYY-MM-DD HH:MM:SS:mmm Don't forget to add an index in case you want to query on it as key |
|||
| By: psadac | Date: 25/09/2004 19:00:00 | |
Type : Comment |
| 4) use BIGINT to store unix timestamp * 1000 with FROM_UNIXTIME() and UNIX_TIMESTAMP() functions. <A HREF="http://www.mysql.com/doc/en/Date_and_time_functions.html">http://www.mysql.com/doc/en/Date_and_time_functions.html</A> should be faster with indexes. 5) use PostgreSQL |
|||
| By: VGR | Date: 25/09/2004 19:14:00 | |
Type : Comment |
| 4) is a good hack :D BUT I looked in the UNIX_TIMESTAMP() function, of course :D and I found it returning no millisecond part. At least that's what the MySql documentation says... |
|||
| By: psadac | Date: 25/09/2004 19:25:00 | |
Type : Assist |
| i haven't verified but you must have a modulo (%) function in mysql ? so field % 1000 will give you the milliseconds. i know it's not very practical but you can't define user functions in mysql, that's the reason of 5) |
|||
| By: VGR | Date: 25/09/2004 19:32:00 | |
Type : Comment |
| 4) they will be zero ! if there's no milliseconds returned by unix_timestamp (as I think it is), they storing that*1000 and then doing a modulo 1000 will just display 0 as milliseconds... If the raw data isn't there, you can't do anything. On the other side, mktime() in PHP has ability to get milliseconds and even fractions , so it's always possible to use a BIGINT or a FLOAT or a CHAR(15+) to store the "normal DATETIME format"+a part of the milliseconds. But not in MySql itself IMHO |
|||
|
Do register to be able to answer |
|||
©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page!
Contact
- Privacy
- Contact us
- Member Agreement
- Jobs for our Experts
- our friend ActuLab
- Carcasherdotcom Seocontest
- Add Your Link
- Bee Web Directory
- Discover Directory
- FFind Directory
- Link Velocity Directory
- The Little Web Directory
- Red Links
- Tranix
- 2space Global Directory
Fully downloaded more than 1313 Million times
Copyright © 2003 - 2012 The IT European Experts Organization
last update on : 23/07/2008 09:41:27 GMT 0 (CEST)
