Database date time how to setup

[Kroonen]

Hi,

I setting up differtent databases but i don;t know what is the most smart setting to punt the time on the database, so you can easy do queries

Now on my database i get a unix timestamp but I think it is not handy
I want to selecct on date hour etc. Must i make a table for hours and date seperate?

-------+---------------------+-------+----------+
| dtKey | time | water | verbruik |
+-------+---------------------+-------+----------+
| 114 | 2008-03-04 22:00:02 | 2442 | 9.5 |
| 113 | 2008-03-04 21:00:01 | 2423 | 0.5 |
| 112 | 2008-03-04 20:00:01 | 2422 | 90.0 |
| 111 | 2008-03-04 19:00:01 | 2242 | 67.5 |
| 110 | 2008-03-04 18:00:02 | 2107 | 7.0 |

[Digit]

A timestamp is okay.
You can use things like DATEPART, DATEDIFF functions in your queries to manipulate the timestamp field. The mentioned functions are for MSSQL, but i'm sure MySQL has similar things. Just wait for the next reply [:)]

[Snelvuur]

You can use mtime or ctime. Just like xpl-perl / zenah does. You can also make query's then based on the examples i showed you for the graphs for xpl-perl. This should also be possible by using a different time date.

But your datetime format can be used easily too, because if you want to search on a given date or time, you can always use %like which works easy.

// Erik (binkey.nl)

[Kroonen]

Well i have found this sort of query

SELECT * from digitemp Where time between "2008-03-05 0:00"and "2008-03-05 23:00";

I must look how to work with weeks, but months and years no problem

[Bwired]

Code: Select all

(SELECT id, SUM(m3), YEAR(datum) AS jaar, WEEK(datum,5) AS t_week, Round(Sum(M3*0.5319556),2) FROM gas GROUP BY jaar, t_week ORDER BY id DESC limit 30) ORDER BY id ASC

[Kroonen]

Pieter,

You have a time field also on field with such an entry?? Or different?

[Bwired]

I have date and time separate but I think that does not matter, the Year and Week function will work.