Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132

    Unanswered: DateTime or TimeStamp or UnixTimeStamp ?

    Let me give you my example:
    I need to manage a shared calendar with the ability
    to create/update/remove sessions containing among other info
    - the location
    - the owner
    - participants
    - the date
    - the time_from time_to
    .
    .

    And I'll need to supply reports with sums of hours (or day counting)
    grouped by whatever info

    I'm wondering how am I going to store date&time, here are my solutions:
    - unix_timestamp_from
    - unix_timestamp_to

    - mysql_datetime_from
    - mysql_datetime_to

    - mysql_date
    - mysql_time_from
    - mysql_time_to

    The question: is what is the best format ?
    I'd like to know the pros/cons for these 3 choices.
    Or do you have another option ?
    Thx.
    Last edited by gtk; 08-01-06 at 09:12.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't have the time to write a long story about the pros and cons of all of those, sorry

    my advice:

    - mysql_datetime_from
    - mysql_datetime_to

    the sql for finding events for a given date range is a lot easier than the other two methods
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    Ok thanks now I'd like to do a sum on several intervals.
    Let's say we have these records:

    Code:
    +---------------------+---------------------+
    | start_time          | end_time            |
    +---------------------+---------------------+
    | 2006-07-01 10:00:00 | 2006-07-01 11:00:00 | <- duration: 1h
    | 2006-06-10 12:00:00 | 2006-06-10 15:30:00 | <- duration: 3.5h
    | 2006-05-12 09:00:00 | 2006-05-12 16:00:00 | <- duration: 7h
    | 2005-08-05 08:00:00 | 2005-08-05 10:00:00 | <- duration: 2h
    +---------------------+---------------------+
    I suppose this doesn't work:
    Code:
    SELECT
    	SUM(TIMEDIFF(end_time, start_time)) AS "total"
    FROM
    	table
    WHERE
    	YEAR(start_time) = 2006
    GROUP BY
    	YEAR(start_time)
    Here is the result that I'd like to get:
    Code:
    +----------+
    | total    |
    +----------+
    | 11:30:00 |
    +----------+

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    TIMEDIFF returns a time, and you can't SUM a time (reliably)

    convert the datetimes to unix timestamps, sum the differences to get seconds, and convert back to time using SEC_TO_TIME --
    Code:
    select sec_to_time(
            sum(
              unix_timestamp(end_time) 
             -unix_timestamp(start_time)
               ) ) as total
      from daTable
     where year(start_time)=2006
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    Ha good idea thanks.

Posting Permissions

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