Login or Register to Ask a Question and Join Our Community


Programming

SQL datetime calculations

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming SQL datetime calculations
# 1  
Old 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?
# 2  
Old 09-13-2009
What's the definition of 'datetime'? It would be difficult to say without knowing anything about the data type.
# 3  
Old 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
# 4  
Old 09-13-2009
Database is MySQL indeed, overlooked that it would matter. Thanks for your elaborate answer.
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Filename with datetime

Hello All, I need unix command to generate a file with datetime in it. For example : ABC_YYYYMMDDHH24MISS.txt Regards Biswajeet Ghosh (1 Reply)
Discussion started by: bghosh
1 Replies

2. Shell Programming and Scripting

SunOS compare datetime

Hi i need to compare the datetime between 2 columns. SunOS 5.1 is used. Notice that mktime seems like not supported. cat file2 P1,2012 12 4 21 36 48,2012 12 4 22 26 53 P2,2012 12 4 20 36 48,2012 12 4 21 21 23 P3,2012 12 4 18 36 48,2012 12 4 20 12 35 Below is the command used.... (1 Reply)
Discussion started by: chailee
1 Replies

3. AIX

AIX DateTime Computation

Good day people, Kindly advice on below please. 1) Formatting/ Arithmetic operation of given date I understand from the AIX man date and some research that flag -d is not applicable for AIX shell scripting and some of the UNIX command date command is not available in AIX. Please advice... (1 Reply)
Discussion started by: cielle
1 Replies

4. Shell Programming and Scripting

Adding 48 hours to DateTime

Hey Guys, I have looked for a solution throughout the forum for my particular question, but I cant find one. So I'm sorry if I overlooked it. I need to be able to 48 add hours to a particular DateTime string. I have a variable named $startTime I would like to be able to take that... (1 Reply)
Discussion started by: chagan02
1 Replies

5. Shell Programming and Scripting

How to compare datetime?

Hi, To get the batch status, I will need to check if the particular job started after 5PM. if the job start time is before 5 pm, then it means that the job has not started for this particular date. I will run the script with date as argument. For eg: BS 07/10/2012 Start time from the log is... (8 Replies)
Discussion started by: ajayakunuri
8 Replies

6. Shell Programming and Scripting

How to generate datetime string?

Hi. I'm hoping there is a simple method where I'm able to generate a datetime string that looks like this (yyyymmddhhmm): 201106280830 The tricky part would be that I need this string to be today's datetime minus 1 year. Is there anyway to do this? (3 Replies)
Discussion started by: buechler66
3 Replies

7. Shell Programming and Scripting

datetime difference in seconds

Hi, I'm trying to find processing time of my script. Please can someone give me the commands to get the start/end time in "dd-mm-yyyy hh:mm:ss" and the differnce in seconds. Thanks! (5 Replies)
Discussion started by: dvah
5 Replies

8. Programming

SQL datetime calculation

Suppose I have a mysql table consisting of measurements taken during irregular intervals as follows: CREATE TABLE data (datetime DATETIME, value INTEGER); mysql> SELECT datetime, value FROM data; +---------------------+---------+ | datetime | value |... (2 Replies)
Discussion started by: figaro
2 Replies

9. Shell Programming and Scripting

datetime.pm

Hi, I'm trying to use datetime.pm function in Perl. I do not have in the library. Is there a way to get it and put it into library? Thanks, George. (1 Reply)
Discussion started by: gpaulose
1 Replies
Login or Register to Ask a Question