Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2013
    Posts
    3

    Unanswered: Very newbie: update table "timestamp" in a relative way? like 'fourweeksago'?

    Hello
    this is my first post and yes, I'm a totally newbie...

    I want to update a table:
    table name:name: tt_news
    cells: pid, crdate

    this is how it works manually:

    UPDATE tt_news
    SET pid='378'
    WHERE pid='44'
    AND crdate <= '1372864562';

    but i want to call the crdate relative, like:
    AND crdate <= 'fourweeksago'; (i know this is not working like this...)

    is there any way to go?
    I will update the table with a cronjob (triggering a updatemytable.sh or something)

    I really appreciate your help!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use one of the MySQL date time functions to do arithmatic on a date time value/
    on the face of it date_sub is the one you want.

    however be careful on your choice of terms 'timestamp' is a data type in MySQL and is the last time this row was edited

    I'm presuming 1372864562 has soem meaning. but you will be better off specifying the point in time as a true date (use ISO format [yyyy/mm/dd])
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2013
    Posts
    3

    thanks

    so it will be like this to get entries older than 30 days, right?

    UPDATE tt_news
    SET pid='378'
    WHERE pid='44'
    AND crdate BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND DATE_SUB(CURDATE(), INTERVAL 60 DAY);

    I will run this script once a week, so 60 days should be fine, no older entries with the pid=44 will be there then.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why not try it and find out?
    rather than wait 8 hours till someone asks the question why not try for yourself

    the BEWTEEN operator should always have the lower value first
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2013
    Posts
    3
    i will. for this i'm duplicating the existing db to not screw it up with my skills
    thanks for your help, i have a lot to learn...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •