Login or Register to Ask a Question and Join Our Community


Programming

SQL datetime calculation

Login to Discuss or Reply to this Discussion in Our Community

 
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:
figaro vivek.goel.piet
# 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