Re: UNIX timestamp with microseconds
Hello.
According to the manual "Unix timestamp (seconds since '1970-01-01
00:00:00'", so, in my opinion UNIX_TIMESTAMP is not designed for
obtaining microseconds. Have a look here, if you haven't done this
yet:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
Ryan Escarez wrote:
> is possible to get UNIX timestamp with microseconds
>
> when i try the following it just give 10 digits
>
> mysql>SELECT unix_timestamp('20051114095641'+ INTERVAL 0 HOUR) as ts;
>
> output :
>
> 1131933401 <--- 10 digits
>
> any tips?
>
> tia!
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unix timestamp
Sebastian wrote: > i have this query: > > SELECT COUNT(*) AS score FROM downloads WHERE date_add(dateline, > interval 1 hour) >= now() GROUP BY filename ORDER BY score DESC > > unfortunately for other reasons i had to change `dateline` to unix > timestamp so this query is no longer able to run as intended. can anyone > help with a work around? > btw, i am using php to run queries if that helps find a solution. > > If the database server and the webserver are not on the same box you probably should use the same source for the timestamps. Translated in plain english the insert query is created on the downloads server and don't use MySQL function to insert the date you should use php time() function to retrieve your data. $sql=" SELECT COUNT(*) AS score FROM downloads WHERE dateline >= " . (time() - 3600) . " GROUP BY filename ORDER BY score DESC "; Also in a ntp syncronized network a difference of one or two second is not unusual. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unix timestamp
Keith Ivey <[EMAIL PROTECTED]> writes: > Scott Gifford wrote: > >> SELECT COUNT(*) AS score FROM downloads WHERE dateline + >> 3600 >= UNIX_TIMESTAMP() GROUP BY filename ORDER BY score DESC > > It would be better with > > WHERE dateline >= UNIX_TIMESTAMP() - 3600 > > so that it can use an index on dateline. You're right; I have too much faith in the optimizer. :-) Scott. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unix timestamp
Scott Gifford wrote: SELECT COUNT(*) AS score FROM downloads WHERE dateline + 3600 >= UNIX_TIMESTAMP() GROUP BY filename ORDER BY score DESC It would be better with WHERE dateline >= UNIX_TIMESTAMP() - 3600 so that it can use an index on dateline. -- Keith Ivey <[EMAIL PROTECTED]> Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unix timestamp
Sebastian <[EMAIL PROTECTED]> writes: > i have this query: > > SELECT COUNT(*) AS score FROM downloads WHERE date_add(dateline, > interval 1 hour) >= now() GROUP BY filename ORDER BY score DESC > > unfortunately for other reasons i had to change `dateline` to unix > timestamp so this query is no longer able to run as intended. can > anyone help with a work around? Sure, 1 hour is just 3600 seconds, which is how Unix timestamps are measured: SELECT COUNT(*) AS score FROM downloads WHERE dateline + 3600 >= UNIX_TIMESTAMP() GROUP BY filename ORDER BY score DESC ScottG. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unix timestamp
Well, you could use the FROM_UNIXTIME() function to convert it into a datetime MySQL understands. SELECT COUNT(*) AS score FROM downloads WHERE date_add(FROM_UNIXTIME(dateline), interval 1 hour) >= now() GROUP BY filename ORDER BY score DESC But, considering what you're doing, it would probably be better if you just skipped all the MySQL date functions and just used UNIX_TIMESTAMP() instead. SELECT COUNT(*) AS score FROM downloads WHERE UNIX_TIMESTAMP() - dateline <= 3600 GROUP BY filename ORDER BY score DESC I haven't tested these, but you should be looking at the manul anyway. Chris FROM_UNIXTIME(): http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html#id2724743 UNIX_TIMESTAMP(): http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html#id2726862 Sebastian wrote: i have this query: SELECT COUNT(*) AS score FROM downloads WHERE date_add(dateline, interval 1 hour) >= now() GROUP BY filename ORDER BY score DESC unfortunately for other reasons i had to change `dateline` to unix timestamp so this query is no longer able to run as intended. can anyone help with a work around? btw, i am using php to run queries if that helps find a solution. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unix timestamp
Sebastian <[EMAIL PROTECTED]> wrote on 08/15/2005 03:51:05 PM: > i have this query: > > SELECT COUNT(*) AS score FROM downloads WHERE date_add(dateline, > interval 1 hour) >= now() GROUP BY filename ORDER BY score DESC > > unfortunately for other reasons i had to change `dateline` to unix > timestamp so this query is no longer able to run as intended. can > anyone help with a work around? > > btw, i am using php to run queries if that helps find a solution. > > > > > > > -- > No virus found in this outgoing message. > Checked by AVG Anti-Virus. > Version: 7.0.338 / Virus Database: 267.10.9/72 - Release Date: 8/14/2005 > One possible solution permutation... SELECT count(*) FROM downloads where dateline > UNIX_TIMESTAMP(now()) - 3600; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Unix-Timestamp() in myODBC 02.50
(answer to myself & anyone interested ;0) After setting my date to "Unix_Timestamp" mode, I convert it to a 10-base number: - >> CONV(UNIX_TIMESTAMP(U.lastTime),10,10) - Then, I perform the substraction: - Select ( CONV(UNIX_TIMESTAMP(U.lastTime),10,10) - CONV(UNIX_TIMESTAMP(D.prevTime),10,10) ) as dateDiff FROM ... - which give the correct results as: dateDiff >> 1294572 >>652223 >> -19864771 This convertion isn't needed when I set up the query in my mySQL-GUI but when executed by VBs/ASP, an empty RecordSet is returned. So firstly, I had to find the cause. After some trial/error, the Unix_Timestamp seemed to be the source of trouble. This convertion is a solution that does the trick. My guess is that the ODBC driver is behaving badly in this case ... Cheers, bart > Van: Bart Goormans > Verzonden: zondag 16 december 2001 9:21 > > > There seems to be a flaw in the myODBC driver > 02.50 when working with the Unix-Timestamp() > function. > > Apparently, the driver doesn't know which > data-type to use... > > When I send the sql statement: > -- > Select > ( UNIX_TIMESTAMP(U.lastTime) - > UNIX_TIMESTAMP(D.prevTime) > ) as dateDiff > FROM ... > -- > > I get an empty recordset, but no Error-message. > > > > When I change to ... > -- > Select > concat( > ( UNIX_TIMESTAMP(U.myTime) - > UNIX_TIMESTAMP(D.latestDwnl) > ) > , ' testDummy' > ) as dateDiff > FROM ... > -- > > I get the results as to be expected > > >> 1294572 testDummy > >>652223 testDummy > >> -19864771 testDummy > > > This workaround would be complete if I could convert > this string to an integer. > Or is there a beter way altogether, to calculate the > difference between two dates? > Not the same as the SUBDATE()-function, is it? > > > best regards, > > bart> - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
