List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:November 29 2001 4:50pm
Subject:Re: Proposal for two (or more) new time and date functions
View as plain text  
Hi!

>>>>> "Jeremy" == Jeremy Zawodny <jzawodn@stripped> writes:

Jeremy> On Wed, Nov 28, 2001 at 01:30:42PM -0800, btjones@stripped wrote:
>> 
>> I think while you've got the hood up, a better method of doing the
>> difference between two dates should be derived.
>> 
>> Unless I'm going about this all wrong, the only way to get the time
>> difference between two values currently is:
>> 
>> UNIX_TIMESTAMP(end_datetime) - UNIX_TIMESTAMP(start_datetime)
>> 
>> Wouldn't something like SECONDS_DIFF(start,end) make more sense?  Or
>> perhaps TIME_DIFF(start,end)?

Jeremy> It's not the only way, but none of them are as simple as a

Jeremy>   SELECT date1 - date2

The problem with implementing the above simple expression is that you
can easily run into problems because of the automatic convert of
strings to numbers.  For example: What should we do if one of the
strings is a date and the other is a number or a string ?

If we would do the above, then we would also be able to handle:

SELECT "2001-01-01" - date from table_name;

SELECT "2001-01-01" - "2000-01-01";

One simple solution is to to only do this if both columns are of type
DATE.  You can always 'cast' a column to date with:

SELECT DATE "2001-01-01" - DATE "2000-01-01";

How would this sound?

Regards,
Monty

Thread
Re: Proposal for two (or more) new time and date functionsbtjones28 Nov
  • Re: Proposal for two (or more) new time and date functionsJeremy Zawodny29 Nov
    • Re: Proposal for two (or more) new time and date functionsMichael Widenius29 Nov
  • Re: Proposal for two (or more) new time and date functionsThomas Spahni29 Nov
    • Re: Proposal for two (or more) new time and date functionsMichael Widenius29 Nov
Re: Proposal for two (or more) new time and date functionsbtjones30 Nov
  • Re: Proposal for two (or more) new time and date functionsSinisa Milivojevic30 Nov