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
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
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
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
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
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
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
(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:
