Sponsored Content
Full Discussion: SQL datetime calculations
Top Forums Programming SQL datetime calculations Post 302352797 by figaro on Sunday 13th of September 2009 12:05:03 PM
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?
 

9 More Discussions You Might Find Interesting

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

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

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

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

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

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

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

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

9. 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
DateTime::Format::W3CDTF(3pm)				User Contributed Perl Documentation			     DateTime::Format::W3CDTF(3pm)

NAME
DateTime::Format::W3CDTF - Parse and format W3CDTF datetime strings SYNOPSIS
use DateTime::Format::W3CDTF; my $w3c = DateTime::Format::W3CDTF->new; my $dt = $w3c->parse_datetime( '2003-02-15T13:50:05-05:00' ); # 2003-02-15T13:50:05-05:00 $w3c->format_datetime($dt); DESCRIPTION
This module understands the W3CDTF date/time format, an ISO 8601 profile, defined at http://www.w3.org/TR/NOTE-datetime. This format as the native date format of RSS 1.0. It can be used to parse these formats in order to create the appropriate objects. METHODS
This API is currently experimental and may change in the future. o new() Returns a new W3CDTF parser object. o parse_datetime($string) Given a W3CDTF datetime string, this method will return a new "DateTime" object. If given an improperly formatted string, this method may die. o format_datetime($datetime) Given a "DateTime" object, this methods returns a W3CDTF datetime string. NOTE: As of version 0.4, format_datetime no longer attempts to truncate datetimes without a time component. This is due to the fact that "DateTime" doesn't distinguish between a date with no time component, and midnight. o format_date($datetime) Given a "DateTime" object, return a W3CDTF datetime string without the time component. SUPPORT
Support for this module is provided via the datetime@perl.org email list. See http://datetime.perl.org/?MailingList for details. Please submit bugs to the CPAN RT system at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=datetime-format-w3cdtf or via email at bug-datetime-format-w3cdtf@rt.cpan.org. AUTHOR
Dave Rolsky <autarch@urth.org> CREDITS
This module is maintained by Gregory Todd Williams <gwilliams@cpan.org>. It was originally created by Kellan Elliott-McCrea <kellan@protest.net>. This module was inspired by DateTime::Format::ICal COPYRIGHT
Copyright (c) 2009 David Rolsky. All rights reserved. This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself. Copyright (c) 2003 Kellan Elliott-McCrea Portions of the code in this distribution are derived from other works. Please see the CREDITS file for more details. The full text of the license can be found in the LICENSE file included with this module. SEE ALSO
datetime@perl.org mailing list http://datetime.perl.org/ perl v5.10.1 2011-02-06 DateTime::Format::W3CDTF(3pm)
All times are GMT -4. The time now is 10:58 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy