Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Reading a tutorial once I think I read there was a classification [ I know this isn't the real term for it but I have no idea what they're called] of a DB for TIME. You know, like you have VARCHAR and INTEGER, you have one for time.

Think this was for MySQL. Anyway, the question is how would you store the time to the field in MySQL time? Or is the time used no different than time()? What I am trying to do is set up a column for time and then delete it's row if it existed for more than 5 minutes.

Any help on this would be much appreciated.

Edited by Chady -- escaped [ to stop it being linked.

Replies are listed 'Best First'.
Re: 5 minutes of time
by Zaxo (Archbishop) on Jun 04, 2005 at 03:43 UTC

    MySQL has a number of time-related types: DATE, DATETIME, TIMESTAMP, TIME, YEAR. If you need to produce a particular format for a type POSIX::strftime() is very handy. Since you want to time out records, TIMESTAMP is probably what you want.

    info mysql will tell you details.

    After Compline,
    Zaxo

[reply]
Re: 5 minutes of time
by monarch (Priest) on Jun 04, 2005 at 03:56 UTC
    You can approach time in databases many different ways..

    But if you're just starting out, and just want to conquer a simple problem - like how to delete rows 5 minutes old, then I suggest a simple way, and it's compatible with every database you'll ever come across:

      CREATE TABLE mytable
  (
      person VARCHAR(20),
      lasttime INTEGER
  );

    [download]
    Read more... (1218 Bytes)
[reply]
[d/l]
[select]
      While I agree with this to an extent, MySQL also has a ton of functions for modifying and comparing dates. The TIMESTAMP type tends to be useless for what the OP is looking for -- as it gets updated each time the record is updated. However, DATETIME does not, and it becomes fairly trivial to write SQL that can do date/time comparisons on such fields. The reason I would prefer to use the SQL over using perl in this instance is that it's portable; if I choose to use another language, I can use the same SQL for doing the comparison.

      I leave it up to the OP to look up the date and time functions; they're very well documented on the MySQL site. (Which is where s/he should have started looking, really... :-)

[reply]
Re: 5 minutes of time
by other90percent (Sexton) on Jun 04, 2005 at 09:21 UTC

    You appear to be asking about date/time "column types" (in DBA terms) for MySQL, and I assume you wish to work with such values conveniently in Perl.

    I'm an OOP fan, so I'd suggest Time::Piece::MySQL . It lets you work with intervals of time, and dissect any date-time you'd like; and it reads and writes exactly what MySQL expects for its 'timestamp', 'datetime' and 'date' column types. And you can easily do addition/subtraction of intervals expressed as seconds, and compare times easily; '+', '-', '<' and so on can be overloaded for you. All that comes from the general interface of Time::Piece .

    Happy coding!

[reply]
Re: 5 minutes of time
by TedPride (Priest) on Jun 04, 2005 at 04:48 UTC
    The built-in time fields are automatically updated whenever mySQL creates or updates a record, but it's not always helpful to change the timestamp on update. I generally do what monarch suggests and store the current timestamp from time() in an INT field, allowing me more control over when to update the timestamp.
[reply]
      The built-in time fields are automatically updated whenever mySQL creates or updates a record

      That is only TIMESTAMP and only the first one in your table. You still have the DATETIME, DATE and TIME ones to choose from. It also allows you to have several timestamps, one which will always update (a "last modified") and others that only change when you want them too.

      It's all about semantics, really. If you want to store a date or a time, store a DATE or a TIME. If you want to stamp a certain time... Also, it never hurts to read up on what the manuals say instead of reinventing another wheel. As with Perl, you can often assume that good products already have the tools you need, and of better quality. Saves time and enhances overall quality.

[reply]

Back to Seekers of Perl Wisdom