SQL datetime calculation


 
Thread Tools Search this Thread
Top Forums Programming SQL datetime calculation
# 1  
Old 04-23-2011
SQL datetime calculation

Suppose I have a mysql table consisting of measurements taken during irregular intervals as follows:

Code:
CREATE TABLE data (datetime DATETIME, value INTEGER);

mysql> SELECT datetime, value FROM data;
+---------------------+---------+
| datetime            | value   |
+---------------------+---------+
| 2010-09-12 13:25:38 |   41538 | 
| 2010-09-12 13:25:55 |   41543 | 
| 2010-09-12 23:01:06 |   43364 | 
| 2010-09-12 23:01:46 |   43365 | 
| 2010-09-12 23:02:40 |   43366 | 
| 2010-09-12 23:02:48 |   43367 | 
| 2010-09-16 19:13:50 |   75538 | 
| 2010-09-16 19:19:27 |   75539 | 
+---------------------+---------+

What I would like to do is select the values which are the maximum in their minute, so the output should be as follows:

Code:
41543
43365
43367
75538
75539

So 41543 is selected because its datetime is 2010-09-12 13:25:55, which is after 2010-09-12 13:25:38. How do I achieve this?
# 2  
Old 04-23-2011
Code:
select t1.b 
  from (
  select       
    max(a) m  
  from       
    data
  group by       
    date_format(a, '%Y%m%d%k%i')
    ) t2
join data t1
on  t1.a = t2.m
order by t1.b;

For example:

Code:
mysql> select * from t;
+---------------------+-------+
| a                   | b     |
+---------------------+-------+
| 2010-09-12 13:25:38 | 41538 |
| 2010-09-12 13:25:55 | 41543 |
| 2010-09-12 23:01:06 | 43364 |
| 2010-09-12 23:01:46 | 43365 |
| 2010-09-12 23:02:40 | 43366 |
| 2010-09-12 23:02:48 | 43367 |
| 2010-09-16 19:13:50 | 75538 |
| 2010-09-16 19:19:27 | 75539 |
+---------------------+-------+
8 rows in set (0.01 sec)

mysql> select t1.b
    ->   from (
    ->   select
    ->     max(a) m
    ->   from
    ->     t
    ->   group by
    ->     date_format(a, '%Y%m%d%k%i')
    -> ) t2
    -> join t t1
    -> on  t1.a = t2.m
    -> order by t1.b;
+-------+
| b     |
+-------+
| 41543 |
| 43365 |
| 43367 |
| 75538 |
| 75539 |
+-------+
5 rows in set (0.01 sec)

These 2 Users Gave Thanks to radoulov For This Post:
# 3  
Old 04-24-2011
Works great. Thank you very much.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 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. UNIX for Dummies Questions & Answers

Find files between two datetime..

Hi All, How do i find all the files between two times. Eg: 26 may 2014 06:00 to 26 may 2014 14:00 mmin and mtime gives for a specific period. we receive hundreds of files input directories and i need to find how many files are received between given specific datetime. Thanks. (2 Replies)
Discussion started by: abhi_123
2 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

Awk new datetime everyline

Hi, I'm using awk in HP-UX machine which does not support systime(), strftime(). So to get the date time I was using : seq 1 100000 | awk ' "date +%Y%m%d%H%M%s" | getline curtime; print curtime }' However the above code gets the date only once, next time it is not updated. For... (2 Replies)
Discussion started by: Random_Net
2 Replies

8. Shell Programming and Scripting

Datetime Format 'yyyymmddmiss' Calculation

Given a time format yyyymmddmiss 201007221403 How can I add or minus minutes such that it becomes 201007221348 when I minus away 15 minutes from the time? Also, given a time frame, let say 10 days, how do I check if a date 201006231403 that is not more than 10 days behind... (3 Replies)
Discussion started by: alienated
3 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

10. Programming

SQL datetime calculations

Suppose I have a table as follows: 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: SELECT D1.datetime,... (3 Replies)
Discussion started by: figaro
3 Replies
Login or Register to Ask a Question