SQL: find if a set od dates falls in another set of dates
Don't know if it is important: Debian Linux / MySQL 5.1
I have a table:
Scenario: I run a report to show all media that was or will be active between two dates, say 10/01/2010 and 10/17/2010.
Now I can write a statement that will give me what i want but it seems like a bad way to do it.
If someone knows of a cleaner more elegant way to do this and is willing to share, I'd be appreciative.
Thanks
Vertical
Last edited by Scott; 10-16-2010 at 07:48 AM..
Reason: Please use code tags
Dear All,
Please can you help me to crack this query?
If the log files for the task above all had a naming convention of myoutput_YearMonthDay.log (i.e. myoutput_20060215) How would you find only those log files created between the 10th and the 20th of each month going back the last 365 days.... (1 Reply)
Hello friends,
I am looking for a script or method that can display all the dates between any 2 given dates.
Input:
Date 1
290109
Date 2
010209
Output:
300109
310109
Please help me. Thanks. :):confused: (2 Replies)
Hi guys,
For my wiki site I need to fix 1400 pages that use the wrong date format, most pages (not all) use eg. 1988]] I need to change that to (1988)]]
The date range goes back to 1400 so I guess I need to do the following
ssh into my server,
dump mysql database
vi .sql dump
search... (20 Replies)
hi All
Please explain the below statement in RED?What does this mean?
perl /HDS/common/operations/Quality_Team/Nirvana/WEEKLY_OOPS/sql_dump.pl --sql "select * from WEEKLY_REPORT order by test_case, type" --username=ddb_qa --password=ddb_qa123 --sid=pldeldb --output... (1 Reply)
Hi All,
I am trying to print the dates that falls between 2 date variables into a file. Here is the example.
$BUS_DATE =20120616
$SUB_DATE=20120613
Output to file abc.txt should be : 20120613,20120614,120120615,20120616
Can you pls help me accomplish this in LINUX.
Thanks... (5 Replies)
HI,
i have row like this
HHH100037440313438961000201001012012073110220002 N
in this i have 2 dates in pos 25-32 and 33-40 , so based upon the se two dates , i need to generated records between these two values
so in the above record 20100101 and 20120731
need to genearte rows like this... (4 Replies)
Hi Am Using Unix Ksh ...
I have a Table called date
select * from date ;
Date
01/02/2013
06/02/2013
I need the output as
Missing Date
01/02/2013
02/02/2013
03/02/2013
04/02/2013
05/02/2013
06/02/2013 (2 Replies)
Hi All,
I have 2 dates in mm/dd format.
sdate=10/01 (October 01)
edate=10/10 (October 10)
I need the dates in between these 2 dates like below.
10/01
10/02
10/03
10/04
10/05
10/06
10/07
10/08 (1 Reply)
How to find the difference between below epoc dates in HH:MM:SS
1557863573 converts to Tuesday May 14, 2019 21:52:53 (pm) in time zone Europe/Amsterdam (CEST)
1557866394 converts to Tuesday May 14, 2019 22:39:54 (pm) in time zone Europe/Amsterdam (CEST)
#!/bin/bash
set -x
A=1557863573... (4 Replies)
Discussion started by: abhaydas
4 Replies
LEARN ABOUT DEBIAN
class::dbi::mysql
Class::DBI::mysql(3pm) User Contributed Perl Documentation Class::DBI::mysql(3pm)NAME
Class::DBI::mysql - Extensions to Class::DBI for MySQL
SYNOPSIS
package Film;
use base 'Class::DBI::mysql';
__PACKAGE__->set_db('Main', 'dbi:mysql:dbname', 'user', 'password');
__PACKAGE__->set_up_table("film");
__PACKAGE__->autoinflate(dates => 'Time::Piece');
# Somewhere else ...
my $type = $class->column_type('column_name');
my @allowed = $class->enum_vals('column_name');
my $tonights_viewing = Film->retrieve_random;
DESCRIPTION
This is an extension to Class::DBI, containing several functions and optimisations for the MySQL database. Instead of setting Class::DBI as
your base class, use this instead.
METHODS
set_up_table
__PACKAGE__->set_up_table("table_name");
Traditionally, to use Class::DBI, you have to set up the columns:
__PACKAGE__->columns(All => qw/list of columns/);
__PACKAGE__->columns(Primary => 'column_name');
Whilst this allows for more flexibility if you're going to arrange your columns into a variety of groupings, sometimes you just want to
create the 'all columns' list. Well, this information is really simple to extract from MySQL itself, so why not just use that?
This call will extract the list of all the columns, and the primary key and set them up for you. It will die horribly if the table contains
no primary key, or has a composite primary key.
autoinflate
__PACKAGE__->autoinflate(column_type => 'Inflation::Class');
__PACKAGE__->autoinflate(timestamp => 'Time::Piece');
__PACKAGE__->autoinflate(dates => 'Time::Piece');
This will automatically set up has_a() relationships for all columns of the specified type to the given class.
We currently assume that all classess passed will be able to inflate and deflate without needing extra has_a arguments, with the example of
Time::Piece objects, which we deal with using Time::Piece::mysql (which you'll have to have installed!).
The special type 'dates' will autoinflate all columns of type date, datetime or timestamp.
create_table
$class->create_table(q{
name VARCHAR(40) NOT NULL PRIMARY KEY,
rank VARCHAR(20) NOT NULL DEFAULT 'Private',
serial INTEGER NOT NULL
});
This creates the table for the class, with the given schema. If the table already exists we do nothing.
A typical use would be:
Music::CD->table('cd');
Music::CD->create_table(q{
cdid MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
artist MEDIUMINT UNSIGNED NOT NULL,
title VARCHAR(255),
year YEAR,
INDEX (artist),
INDEX (title)
});
Music::CD->set_up_table;
drop_table
$class->drop_table;
Drops the table for this class, if it exists.
column_type
my $type = $class->column_type('column_name');
This returns the 'type' of this table (VARCHAR(20), BIGINT, etc.)
enum_vals
my @allowed = $class->enum_vals('column_name');
This returns a list of the allowable values for an ENUM column.
retrieve_random
my $film = Film->retrieve_random;
This will select a random row from the database, and return you the relevant object.
(MySQL 3.23 and higher only, at this point)
SEE ALSO
Class::DBI. MySQL (http://www.mysql.com/)
AUTHOR
Tony Bowden
BUGS and QUERIES
Please direct all correspondence regarding this module to:
bug-Class-DBI-mysql@rt.cpan.org
COPYRIGHT AND LICENSE
Copyright (C) 2001-2005 Tony Bowden.
This program is free software; you can redistribute it and/or modify it under
the terms of the GNU General Public License; either version 2 of the License,
or (at your option) any later version.
This program is distributed in the hope that it will be useful, but WITHOUT
ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR A PARTICULAR PURPOSE.
perl v5.10.0 2005-09-03 Class::DBI::mysql(3pm)