|
|
|
google unix.com
|
|||||||
| Forums | Register | Forum Rules | Links | Albums | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
| High Level Programming Post questions about C, C++, Java, SQL, and other programming languages here. |
More UNIX and Linux Forum Topics You Might Find Helpful
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Need help with Date calculations in ksh | jidsh | Shell Programming and Scripting | 5 | 04-23-2009 03:19 PM |
| calculations in awk/sed | anchal_khare | Shell Programming and Scripting | 3 | 03-26-2009 11:41 PM |
| ksh, calculations using bc | cesarNZ | Shell Programming and Scripting | 2 | 12-14-2008 10:42 PM |
| DateTime Format Conversion in a File | srikanthgr1 | Shell Programming and Scripting | 4 | 10-23-2007 01:58 PM |
| rename file to file.ext.datetime | tripsat | Shell Programming and Scripting | 2 | 09-20-2007 05:12 PM |
 |
Powered by 
|
|
|
LinkBack | Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
|
09-13-2009
|
||||
|
||||
|
SQL datetime calculations
Suppose I have a table as follows: Code:
CREATE TABLE data ( `datetime` datetime DEFAULT NOT NULL, `temperature` float DEFAULT NO NULL ); populated with temperature samples of a couple times a second. Let's say I want to find the temperatures which are 1 second apart: Code:
SELECT D1.datetime, D2.datetime, D1.temperature, D2.temperature FROM data AS D1, data AS D2 WHERE D1.datetime - D2.datetime = 1; And if I want to find the temperatures which are 1 minute apart: Code:
SELECT D1.datetime, D2.datetime, D1.temperature, D2.temperature FROM data AS D1, data AS D2 WHERE D1.datetime - D2.datetime = 100; What I dont understand is, why there is a multiplier of 100 and not 60, since there are only 60 seconds in a minute? |
|
09-13-2009
|
||||
|
||||
|
What's the definition of 'datetime'? It would be difficult to say without knowing anything about the data type.
|
|
09-13-2009
|
|||||
|
|||||
|
You haven't mentioned it, but I believe your database is MySQL. (At least it's not Oracle, which behaves way differently than this.) When you add a number to a DATETIME value in MySQL, it gets converted to a numeric double value, with a microseconds part of .000000, i.e. YYYYMMDDHHMISS.000000. In this numeric value, an increase of 100 corresponds to an interval of 1 minute of the datetime. You can test that by casting such a numeric value back to DATETIME. (If it is invalid, the CAST will return NULL.) Code:
mysql>
mysql>
mysql> select d, d+0 d0, d+60 d2, d+100 d4,
-> cast(d+60 as datetime) c1,
-> cast(d+100 as datetime) c2
-> from (select cast('2009-01-01 10:11:12' as datetime) d) t;
+---------------------+-----------------------+-----------------------+-----------------------+------+---------------------+
| d | d0 | d2 | d4 | c1 | c2 |
+---------------------+-----------------------+-----------------------+-----------------------+------+---------------------+
| 2009-01-01 10:11:12 | 20090101101112.000000 | 20090101101172.000000 | 20090101101212.000000 | NULL | 2009-01-01 10:12:12 |
+---------------------+-----------------------+-----------------------+-----------------------+------+---------------------+
1 row in set, 1 warning (0.00 sec)
mysql>
mysql>
A correct way to work with DATETIME differences is to use the INTERVAL keyword. Your query should've been this - Code:
mysql> mysql> mysql> desc t; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | dtime | datetime | NO | | NULL | | | temp | float | NO | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> select dtime, temp from t; +---------------------+------+ | dtime | temp | +---------------------+------+ | 2009-01-01 06:07:08 | 70.4 | | 2009-01-01 07:08:09 | 72.6 | | 2009-01-01 08:09:10 | 69.3 | | 2009-01-01 06:08:08 | 62.1 | | 2009-01-01 07:08:10 | 63.2 | | 2009-01-01 11:12:13 | 64.3 | | 2009-01-01 06:09:08 | 78.8 | | 2009-01-01 07:08:11 | 79.9 | +---------------------+------+ 8 rows in set (0.00 sec) mysql> mysql> # fetch all pairs of records that have a difference of 1 minute between their DTIME values mysql> mysql> SELECT D1.dtime, D2.dtime, D1.temp, D2.temp FROM t AS D1, t AS D2 WHERE D1.dtime = D2.dtime + interval 1 minute; +---------------------+---------------------+------+------+ | dtime | dtime | temp | temp | +---------------------+---------------------+------+------+ | 2009-01-01 06:08:08 | 2009-01-01 06:07:08 | 62.1 | 70.4 | | 2009-01-01 06:09:08 | 2009-01-01 06:08:08 | 78.8 | 62.1 | +---------------------+---------------------+------+------+ 2 rows in set (0.00 sec) mysql> mysql> HTH, tyler_durden |
|
| Bookmarks |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
Hybrid Mode
