DateTime or TimeStamp or UnixTimeStamp ?

gtk · #1 (**permalink**) 07-31-06, 07:07

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.

r937 · #2 (**permalink**) 08-04-06, 07:30

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

gtk · #3 (**permalink**) 08-07-06, 09:35

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 |
+----------+

r937 · #4 (**permalink**) 08-07-06, 09:57

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

gtk · #5 (**permalink**) 08-08-06, 03:04

Ha good idea thanks.

Internet Services

Web Development

Digital Marketing

Consumer Tech