Re: Date arithmetic: 2005-08-31 - 1

[Peter Brawley]

Barbara,

> ... I don't know if I'm being asked to add or subtract days...

Why would you want to know that? ADDDATE() doesn't care:

SET @x = -1;
SELECT ADDDATE('1975-1-1', INTERVAL @x DAY);
+--+
| ADDDATE('1975-1-1', INTERVAL @x DAY) |
+--+
| 1974-12-31   |
+--+

PB

-

In ADDDATE( date_value, INTERVAL expr DAYS), 'expr' can resolve to a
positive or negative int.

PB


Barbara Deaton wrote:

  Unfortunately no, because I don't know if I'm being asked to add or subtract days.  I'm just given a value, and have to transform that into something that can be added or subtracted. 

So for example, all I get with is value 1 meaning 1 day and I need to do something with a date, for db2 through some calculations I turn this into 0001. and my resulting SQL statement then becomes:

select count(*) from cwdd where col2 - 0001. = {d '2005-06-07'}

And yes, the period is required for DB2.

I'm trying to figure out what calculation or modifications I need to do to the value passed in, in this case 1.  So that I can turn it into something I can pass down for MySQL to do the math on.

Thanks for the suggestion though, it just won't work in this case.

-Barb.

-Original Message-
From: Freddie Sorensen [mailto:[EMAIL PROTECTED]] 
Sent: Tuesday, August 23, 2005 3:45 PM
To: Barbara Deaton; mysql@lists.mysql.com
Subject: AW: Date arithmetic: 2005-08-31 - 1

Barbara,

Can't you use the ADDDATE function ?

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

Freddie 

  
  
-Ursprüngliche Nachricht-
Von: Barbara Deaton [mailto:[EMAIL PROTECTED]]
Gesendet: Dienstag, 23. August 2005 21:37
An: mysql@lists.mysql.com
Betreff: Date arithmetic: 2005-08-31 - 1

All,

I know MySQL comes with all sorts of wonderful functions to do date 
arithmetic, the problem is the context that my application is being 
called in I don't know if a user wants me to add or subtract days.  
I'm just given the number of days that need to be either added or 
subtracted from the date given.

So for example, if your table was

mysql> select * from dtinterval;
+
| datecol
+
2005-09-01
2005-08-30
2005-08-31
+--

a user could enter:

select count(*) from dtinterval where datecol - 1 = '30AUG2005'd;

Which is our applications SQL, my part of the product is only give the 
value 1, I have to transform that into something MySQL will understand 
as 1 day and then pass that back into the SQL statement to be passed 
down to the MySQL database.  I transform our applications SQL into  
select COUNT(*) from `dtinterval` where (`dtinterval`.`datecol` - 1) =  
'1974-12-04'

I know that just doing the -1 is wrong, since "select '2005-08-31' - 1 
and that just gives me a year

mysql> select '2005-08-31' - 1;
+--+
| '2005-08-31' - 1 |
+--+
| 2004 |
+--+

What do I need to translate the 1 into in order to get back the value 
'2005-08-30' ?

Thanks for your help.
Barbara

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]



  
  


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


  



No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.10.15/80 - Release Date: 8/23/2005


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Date arithmetic: 2005-08-31 - 1

[Michael Stassen]

[EMAIL PROTECTED] wrote:



You are right. There are a lot of date functions in MySQL. However, I am 
not sure exactly what your needs are...


I think you are trying to compute date intervals (did you NOT see the 
INTERVAL keyword when you RTFM?). Most of the date functions currently 
supported are for v4.1 or newer and you didn't say which version you are 
on.  The pre-v4.1 way of doing date math is to convert your datetime 
values into seconds, add or subtract some number of seconds, then convert 
your answer back into a date. To do that you need the 
UNIX_TIMESTAMP()/FROM_UNIXTIME() pair of functions (

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html)



Time to reread the fine manual.  DATE_ADD() and DATE_SUB() existed in mysql 
3.23.


Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Date arithmetic: 2005-08-31 - 1

[Michael Stassen]

Barbara Deaton wrote:
> All,
>
> I know MySQL comes with all sorts of wonderful functions to do date
> arithmetic, the problem is the context that my application is being
> called in I don't know if a user wants me to add or subtract days.  I'm
> just given the number of days that need to be either added or subtracted
> from the date given.
>
> So for example, if your table was
>
> mysql> select * from dtinterval;
> +
> | datecol
> +
> 2005-09-01
> 2005-08-30
> 2005-08-31
> +--
>
> a user could enter:
>
> select count(*) from dtinterval where datecol - 1 = '30AUG2005'd;
>
> Which is our applications SQL, my part of the product is only give the
> value 1, I have to transform that into something MySQL will understand as
> 1 day and then pass that back into the SQL statement to be passed down to
> the MySQL database.  I transform our applications SQL into
>  select COUNT(*) from `dtinterval` where (`dtinterval`.`datecol` - 1) = 
'1974-12-04'

>
> I know that just doing the -1 is wrong, since "select '2005-08-31' - 1
> and that just gives me a year
>
> mysql> select '2005-08-31' - 1;
> +--+
> | '2005-08-31' - 1 |
> +--+
> | 2004 |
> +--+
>
> What do I need to translate the 1 into in order to get back the value
> '2005-08-30' ?
>
> Thanks for your help.
> Barbara

The answer is contained in the previous replies, and in the referenced 
manual page.  Translate the 1 into


  INTERVAL 1 DAY

So your query becomes

  select COUNT(*) from `dtinterval`
  where `dtinterval`.`datecol` - INTERVAL 1 DAY =  '1974-12-04';

In general, n days becomes INTERVAL n DAY, so the query becomes

  select COUNT(*) from `dtinterval`
  where `dtinterval`.`datecol` - INTERVAL n DAY =  '1974-12-04';

That isn't the right way to do it, however.  This query compares a value 
which depends on the row with a constant.  No index on datecol can be used 
to satisfy this query.  You get a full-table scan, with the date calculation 
done on every row.  Always write your WHERE clauses to avoid calculations 
involving row values, if possible.  In this case, your query should be


  select COUNT(*) from `dtinterval`
  where `dtinterval`.`datecol` =  '1974-12-04' + INTERVAL n DAY;

Adding n days to the constant date on the right results in a constant, so it 
can be done once, then the resulting constant can be compared with the 
values of datecol.  In this case, an index on datecol can be used to make 
this quick.


Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Date arithmetic: 2005-08-31 - 1

[Barbara Deaton]

Unfortunately no, because I don't know if I'm being asked to add or subtract 
days.  I'm just given a value, and have to transform that into something that 
can be added or subtracted. 

So for example, all I get with is value 1 meaning 1 day and I need to do 
something with a date, for db2 through some calculations I turn this into 
0001. and my resulting SQL statement then becomes:

select count(*) from cwdd where col2 - 0001. = {d '2005-06-07'}

And yes, the period is required for DB2.

I'm trying to figure out what calculation or modifications I need to do to the 
value passed in, in this case 1.  So that I can turn it into something I can 
pass down for MySQL to do the math on.

Thanks for the suggestion though, it just won't work in this case.

-Barb.

-Original Message-
From: Freddie Sorensen [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 23, 2005 3:45 PM
To: Barbara Deaton; mysql@lists.mysql.com
Subject: AW: Date arithmetic: 2005-08-31 - 1

Barbara,

Can't you use the ADDDATE function ?

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

Freddie 

> -Ursprüngliche Nachricht-
> Von: Barbara Deaton [mailto:[EMAIL PROTECTED]
> Gesendet: Dienstag, 23. August 2005 21:37
> An: mysql@lists.mysql.com
> Betreff: Date arithmetic: 2005-08-31 - 1
> 
> All,
> 
> I know MySQL comes with all sorts of wonderful functions to do date 
> arithmetic, the problem is the context that my application is being 
> called in I don't know if a user wants me to add or subtract days.  
> I'm just given the number of days that need to be either added or 
> subtracted from the date given.
> 
> So for example, if your table was
> 
> mysql> select * from dtinterval;
> +
> | datecol
> +
> 2005-09-01
> 2005-08-30
> 2005-08-31
> +--
> 
> a user could enter:
> 
> select count(*) from dtinterval where datecol - 1 = '30AUG2005'd;
> 
> Which is our applications SQL, my part of the product is only give the 
> value 1, I have to transform that into something MySQL will understand 
> as 1 day and then pass that back into the SQL statement to be passed 
> down to the MySQL database.  I transform our applications SQL into  
> select COUNT(*) from `dtinterval` where (`dtinterval`.`datecol` - 1) =  
> '1974-12-04'
> 
> I know that just doing the -1 is wrong, since "select '2005-08-31' - 1 
> and that just gives me a year
> 
> mysql> select '2005-08-31' - 1;
> +--+
> | '2005-08-31' - 1 |
> +--+
> | 2004 |
> +--+
> 
> What do I need to translate the 1 into in order to get back the value 
> '2005-08-30' ?
> 
> Thanks for your help.
> Barbara
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Date arithmetic: 2005-08-31 - 1

[SGreen]

"Barbara Deaton" <[EMAIL PROTECTED]> wrote on 08/23/2005 03:36:52 PM:

> All,
> 
> I know MySQL comes with all sorts of wonderful functions to do date 
> arithmetic, the problem is the context that my application is being 
> called in I don't know if a user wants me to add or subtract days. 
> I'm just given the number of days that need to be either added or 
> subtracted from the date given.
> 
> So for example, if your table was
> 
> mysql> select * from dtinterval;
> +
> | datecol 
> +
> 2005-09-01
> 2005-08-30
> 2005-08-31 
> +--
> 
> a user could enter:
> 
> select count(*) from dtinterval where datecol - 1 = '30AUG2005'd;
> 
> Which is our applications SQL, my part of the product is only give 
> the value 1, I have to transform that into something MySQL will 
> understand as 1 day and then pass that back into the SQL statement 
> to be passed down to the MySQL database.  I transform our 
> applications SQL into 
>  select COUNT(*) from `dtinterval` where (`dtinterval`.`datecol` - 
> 1) =  '1974-12-04'
> 
> I know that just doing the -1 is wrong, since "select '2005-08-31' -
> 1 and that just gives me a year
> 
> mysql> select '2005-08-31' - 1;
> +--+
> | '2005-08-31' - 1 |
> +--+
> | 2004 |
> +--+
> 
> What do I need to translate the 1 into in order to get back the 
> value '2005-08-30' ?
> 
> Thanks for your help.
> Barbara
> 

You are right. There are a lot of date functions in MySQL. However, I am 
not sure exactly what your needs are...

I think you are trying to compute date intervals (did you NOT see the 
INTERVAL keyword when you RTFM?). Most of the date functions currently 
supported are for v4.1 or newer and you didn't say which version you are 
on.  The pre-v4.1 way of doing date math is to convert your datetime 
values into seconds, add or subtract some number of seconds, then convert 
your answer back into a date. To do that you need the 
UNIX_TIMESTAMP()/FROM_UNIXTIME() pair of functions (
http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html)

The pre v4.1 way of computing a date range (it even works with 
non-integral ranges as in my example)

SET @basedate = '2005-05-03 16:00:00', @daysplusminus = 1.5;
SET @baseunix = UNIX_TIMESTAMP(@basedate), @secsplusminus = 
[EMAIL PROTECTED];
SET @startdate = FROM_UNIXTIME(@baseunix - @secsplusminus), @enddate = 
FROM_UNIXTIME(@baseunix + @secsplusminus);
SELECT @startdate, @enddate;
+-+-+
| @startdate  | @enddate|
+-+-+
| 2005-05-02 04:00:00 | 2005-05-05 04:00:00 |
+-+-+
1 row in set (0.00 sec)


The post v4.1 way (uses only integral days):

SET @basedate = '2005-05-31', @daysplusminus = 1.5;
SET @startdate = @basedate - interval @daysplusminus day, @enddate = 
@basedate + interval @daysplusminus day;
SELECT @startdate, @enddate;
+++
| @startdate | @enddate   |
+++
| 2005-05-30 | 2005-06-01 |
+++
1 row in set (0.00 sec)

Does that help?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

RE: Date arithmetic: 2005-08-31 - 1

[Gordon Bruce]

Do You know about INTERVAL?

 

 Use it in an exprecssion or funtion as 

 

 

..INTERVAL expr type 

where expr is any numerical value

 

*   The INTERVAL keyword and the type specifier are not case
sensitive. 

The following table shows how the type and expr arguments are related: 

type Value 

Expected expr Format 

MICROSECOND

MICROSECONDS 

SECOND

SECONDS 

MINUTE

MINUTES 

HOUR

HOURS 

DAY

DAYS 

WEEK

WEEKS 

MONTH

MONTHS 

QUARTER

QUARTERS 

YEAR

YEARS 

SECOND_MICROSECOND

'SECONDS.MICROSECONDS' 

MINUTE_MICROSECOND

'MINUTES.MICROSECONDS' 

MINUTE_SECOND

'MINUTES:SECONDS' 

HOUR_MICROSECOND

'HOURS.MICROSECONDS' 

HOUR_SECOND

'HOURS:MINUTES:SECONDS' 

HOUR_MINUTE

'HOURS:MINUTES' 

DAY_MICROSECOND

'DAYS.MICROSECONDS' 

DAY_SECOND

'DAYS HOURS:MINUTES:SECONDS' 

DAY_MINUTE

'DAYS HOURS:MINUTES' 

DAY_HOUR

'DAYS HOURS' 

YEAR_MONTH

'YEARS-MONTHS' 

 

 

 

mysql> select min(addr_id) from addresses;

+--+

| min(addr_id) |

+--+

|2 |

+--+

1 row in set (0.00 sec)

 

mysql> select now() + INTERVAL min(addr_ID) Day from addresses;

+---+

| now() + INTERVAL min(addr_ID) Day |

+---+

| 2005-08-25 15:38:15   |

+---+

1 row in set (0.00 sec)

 

mysql> select now()

-> ;

+-+

| now()   |

+-+

| 2005-08-23 15:38:31 |

+-+

1 row in set (0.00 sec)

 

-Original Message-
From: Barbara Deaton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 23, 2005 2:37 PM
To: mysql@lists.mysql.com
Subject: Date arithmetic: 2005-08-31 - 1

 

All,

 

I know MySQL comes with all sorts of wonderful functions to do date
arithmetic, the problem is the context that my application is being
called in I don't know if a user wants me to add or subtract days.  I'm
just given the number of days that need to be either added or subtracted
from the date given.

 

So for example, if your table was

 

mysql> select * from dtinterval;

+

| datecol  

+

2005-09-01

2005-08-30

2005-08-31 

+--

 

a user could enter:

 

select count(*) from dtinterval where datecol - 1 = '30AUG2005'd;

 

Which is our applications SQL, my part of the product is only give the
value 1, I have to transform that into something MySQL will understand
as 1 day and then pass that back into the SQL statement to be passed
down to the MySQL database.  I transform our applications SQL into 

 select COUNT(*) from `dtinterval` where (`dtinterval`.`datecol` - 1) =
'1974-12-04'

 

I know that just doing the -1 is wrong, since "select '2005-08-31' - 1
and that just gives me a year

 

mysql> select '2005-08-31' - 1;

+--+

| '2005-08-31' - 1 |

+--+

| 2004 |

+--+

 

What do I need to translate the 1 into in order to get back the value
'2005-08-30' ?

 

Thanks for your help.

Barbara

 

-- 

MySQL General Mailing List

For list archives: http://lists.mysql.com/mysql

To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]