Joshua Chamas wrote:
> Hey,
>
> I am trying to get the number of seconds between
> the min and max of a datetime in a table...
>
> The cleanest way to get a date difference I thought
> would have been:
>
> mysql> select max(creation) - min(creation) from hits;
> +-------------------------------+
> | max(creation) - min(creation) |
> +-------------------------------+
> | 0 |
> +-------------------------------+
>
> but datetime defaults to the year in arithmetic context,
> and I don't see any date range / period operators that
> can get me resolution to seconds...
>
> mysql> select period_diff(max(creation), min(creation)) from hits;
> +-------------------------------------------+
> | period_diff(max(creation), min(creation)) |
> +-------------------------------------------+
> | 0 |
> +-------------------------------------------+
>
> Any ideas ?? If MySQL can't do this kind of stuff for me,
> I'll have to store unix timestamps in the database, which
> will be awful, and will make my port much more difficult
> between Oracle & MySQL.
select unix_timestamp(max(creation)) - unix_timestamp(min(creation)) from
hits;
However, this seems to prevent MySQL from using indexes, at least in
3.22.22.
Why not just select the max and min and do the subtraction in your
application? That would be the most database-independent way of doing it.
Dan
--
Dan Koch
Webmaster
American City Business Journals
http://www.amcity.com/
