Re: [mysql-144] MySQL date problem

From: Mark Imbriaco
Sent on: Tuesday, June 9, 2009 12:08 AM

Hi Mike,

I whipped this up in a few minutes.  It seems to meet your needs, but I have no idea how fast it is.  The algorithm is fairly simple, subtract DAY()-1 from the given date, to get the first day of the current month, then subtract one month to get the first day of the previous month, then add 14 days to get the 15th day of the previous month.

mysql> SELECT * FROM dt;  
+------------+
| d          |
+------------+
| 2009-05-16 | 
| 2009-02-28 | 
| 2009-03-01 | 
| 2009-04-15 | 
+------------+
4 rows in set (0.00 sec)

mysql> SELECT d, DATE_ADD(DATE_SUB(DATE_SUB(d, INTERVAL (DAY(d)-1) DAY), INTERVAL 1 MONTH), INTERVAL 14 DAY) AS prev15 FROM dt ORDER BY d;
+------------+------------+
| d          | prev15     |
+------------+------------+
| 2009-01-01 | 2008-12-15 | 
| 2009-02-28 | 2009-01-15 | 
| 2009-03-01 | 2009-02-15 | 
| 2009-04-15 | 2009-03-15 | 
| 2009-05-16 | 2009-04-15 | 
+------------+------------+
5 rows in set (0.00 sec)

Hope this helps,
-Mark

On Jun 8, 2009, at 11:26 PM, Michael Rulison wrote:

Dear SQL-ers,

I have problem that is large for  me but I hope it is  small for you.

Data

MySQL table  with unix  date field

Goal: reset date from yyyy-mm-01 to  yyyy-prior-mm-15

Thus, `2009-07-01` would be updated to `2009-06-15`. Actually,  I would
like any July date  to be set to June-15.

Along the way I want to select for some  other attributes and  all dates
for  2009 from July through December but those selections I  have coped
with already.

I have been futzing around with DATE(date-field,  INTERVAL  -15  DAY)  
and some others  like  DATE_SUB(   ), but I keep getting the  syntax wrong.

Many thanks for any help.

Mike Rulison




--
Please Note: If you hit "REPLY", your message will be sent to everyone on this mailing list ([address removed])
http://mysql.meetup.com/144/
This message was sent by Michael Rulison ([address removed]) from The Raleigh MySQL Meetup Group.
To learn more about Michael Rulison, visit his/her member profile: http://mysql.meetup.com/144/members/7328344/
To unsubscribe or to update your mailing list settings, click here: http://www.meetup.com/account/comm/
Meetup Support: [address removed]
632 Broadway, New York, NY 10012 USA


Our Sponsors

  • TEKsystems

    Meeting space and refreshments

  • indieconf 2013

    $20 discount off indieconf 2013 - use code TRIANGLEPHP to save today!

People in this
Meetup are also in:

Log in

Not registered with us yet?

Sign up

Meetup members, Log in

By clicking "Sign up" or "Sign up using Facebook", you confirm that you accept our Terms of Service & Privacy Policy