Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: SQL datetime calculation
Top Forums Programming SQL datetime calculation Post 302516431 by figaro on Saturday 23rd of April 2011 04:32:39 AM
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?
 

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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

LEARN ABOUT OSX

locale::currency

Locale::Currency(3pm)					 Perl Programmers Reference Guide				     Locale::Currency(3pm)

NAME

       Locale::Currency - standard codes for currency identification

SYNOPSIS

	   use Locale::Currency;

	   $curr = code2currency('usd');     # $curr gets 'US Dollar'
	   $code = currency2code('Euro');    # $code gets 'eur'

	   @codes   = all_currency_codes();
	   @names   = all_currency_names();

DESCRIPTION

       The "Locale::Currency" module provides access to standard codes used for identifying currencies and funds, such as those defined in ISO
       4217.

       Most of the routines take an optional additional argument which specifies the code set to use. If not specified, the default ISO 4217
       three-letter codes will be used.

SUPPORTED CODE SETS

       There are several different code sets you can use for identifying currencies. A code set may be specified using either a name, or a
       constant that is automatically exported by this module.

       For example, the two are equivalent:

	  $curr = code2currency('usd','alpha');
	  $curr = code2currency('usd',LOCALE_CURR_ALPHA);

       The codesets currently supported are:

       alpha, LOCALE_CURR_ALPHA
	   This is a set of three-letter (uppercase) codes from ISO 4217 such as EUR for Euro.

	   Two of the codes specified by the standard (XTS which is reserved for testing purposes and XXX which is for transactions where no
	   currency is involved) are omitted.

	   This is the default code set.

       num, LOCALE_CURR_NUMERIC
	   This is the set of three-digit numeric codes from ISO 4217.

ROUTINES

       code2currency ( CODE [,CODESET] )
       currency2code ( NAME [,CODESET] )
       currency_code2code ( CODE ,CODESET ,CODESET2 )
       all_currency_codes ( [CODESET] )
       all_currency_names ( [CODESET] )
       Locale::Currency::rename_currency  ( CODE ,NEW_NAME [,CODESET] )
       Locale::Currency::add_currency  ( CODE ,NAME [,CODESET] )
       Locale::Currency::delete_currency  ( CODE [,CODESET] )
       Locale::Currency::add_currency_alias  ( NAME ,NEW_NAME )
       Locale::Currency::delete_currency_alias	( NAME )
       Locale::Currency::rename_currency_code  ( CODE ,NEW_CODE [,CODESET] )
       Locale::Currency::add_currency_code_alias  ( CODE ,NEW_CODE [,CODESET] )
       Locale::Currency::delete_currency_code_alias  ( CODE [,CODESET] )
	   These routines are all documented in the Locale::Codes::API man page.

SEE ALSO

       Locale::Codes
	   The Locale-Codes distribution.

       Locale::Codes::API
	   The list of functions supported by this module.

       http://www.iso.org/iso/support/currency_codes_list-1.htm
	   The ISO 4217 data.

AUTHOR

       See Locale::Codes for full author history.

       Currently maintained by Sullivan Beck (sbeck@cpan.org).

COPYRIGHT

	  Copyright (c) 1997-2001 Canon Research Centre Europe (CRE).
	  Copyright (c) 2001	  Michael Hennecke
	  Copyright (c) 2001-2010 Neil Bowers
	  Copyright (c) 2010-2012 Sullivan Beck

       This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

perl v5.16.2							    2012-10-11						     Locale::Currency(3pm)
All times are GMT -4. The time now is 07:05 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy