List:General Discussion« Previous MessageNext Message »
From:Daniel Koch Date:September 9 1999 5:08am
Subject:Re: ? how to get seconds between max & min of datetime
View as plain text  
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/



Thread
? how to get seconds between max & min of datetimeJoshua Chamas9 Sep
  • Re: ? how to get seconds between max & min of datetimeDaniel Koch9 Sep
    • Re: ? how to get seconds between max & min of datetimeMichael Widenius9 Sep