Sponsored Content
Top Forums Programming MYSQL query search between dates Post 302926569 by barrydocks on Tuesday 25th of November 2014 08:09:45 AM
Old 11-25-2014
MYSQL query search between dates

Just a little help if possible
I have a table with date data, I want to select dates that fall between today and 1 month ago, here's my query
Code:
SELECT id, stdate, DATE_SUB(CURDATE(), INTERVAL 1 month) as monthago, CURDATE() as today 
FROM data_table
where (stdate between 'today' and 'monthago')

But this return nothing, also tried swapping the dates
Code:
SELECT id, stdate, DATE_SUB(CURDATE(), INTERVAL 1 month) as monthago, CURDATE() as today 
FROM data_table
where (stdate between 'monthago' and 'today')

This
Code:
SELECT id, stdate, DATE_SUB(CURDATE(), INTERVAL 1 month) as monthago, CURDATE() as today 
FROM data_table

returns the columns without an issue, what am I missing?


Also if I use:
Code:
SELECT id, stdate, DATE_SUB(CURDATE(), INTERVAL 1 month) as 'monthago', CURDATE() as 'today'  FROM data_table where ('stdate' between 'monthago' and 'today')

it listed the entire table regardless of the stdate

Last edited by barrydocks; 11-25-2014 at 09:17 AM.. Reason: extra stuff
 

6 More Discussions You Might Find Interesting

1. Programming

How to query one to many mysql

Hi there, I have a hierarchical database that include 4 tables. Table A is the parent of B, B is Parent of C, C is parent of D. If I want to query everything in D that is associated with A.name, how do I do that? Thanks! YanYan (0 Replies)
Discussion started by: pinkgladiator
0 Replies

2. Web Development

mysql query help

hello all i have 2 columns every column in the following format column1 2011-04-01 11:39:54 column2 2019-02-03 00:00:00 i want get difference between above data as following 2 days 11:39 how to do so ? i tried many functions but nothing works please advice what is the query... (6 Replies)
Discussion started by: mogabr
6 Replies

3. Shell Programming and Scripting

mysql query in shellscript

Hi, I want to run below query on shellscript but having one problm. ADV=$( mysql -h "$IP_ADDR" -u "$USER_NAME" "$TABLE_NAME" -BNe" SELECT ADV FROM indata where inid='$INSTRUID' and Date='$latest Date';" ) here Date column contans different below dates 2011-12-01... (11 Replies)
Discussion started by: aish11
11 Replies

4. Programming

mysql query help

Hello i have created mysql query to compare to values and get difference in percentage as following: SELECT file_name, 100 - ((100 * (SELECT file_count FROM xipi_files z WHERE x.file_group = z.file_group AND x.file_name = z.file_name AND z.insert_date = CURDATE( ) - INTERVAL 1 DAY)) /... (1 Reply)
Discussion started by: mogabr
1 Replies

5. Shell Programming and Scripting

Search pattern on logfile and search for day/dates and skip duplicate lines if any

Hi, I've written a script to search for an Oracle ORA- error on a log file, print that line and the .trc file associated with it as well as the dateline of when I assumed the error occured. In most it is the first dateline previous to the error. Unfortunately, this is not a fool proof script.... (2 Replies)
Discussion started by: newbie_01
2 Replies

6. Programming

Need help in mysql query

Hi All, i have a table in mysql with the following data Table name Test Assettype Serial_No Status location Mouse 123456 In Stock chennai Mouse 98765 Allocated chennai Keyboard ... (2 Replies)
Discussion started by: venkitesh
2 Replies
ACCOUNTING(1)							      SMPPPD							     ACCOUNTING(1)

NAME
accounting - calculates statistics about your internet connections DESCRIPTION
accounting is a simple program to calculate statistics about your internet dial-up connections that are started and stopped by either ifup and ifdown or by a frontend to smpppd like cinternet , qinternet or kinternet. OPTIONS
These options are recognized by accounting. If a long option shows an argument as mandatory, then it is mandatory for the equivalent short option also. -l, --last=NUM Only consider the last NUM connections. -a, --after=DATE Only consider connections started after DATE. DATE is a date with optional time in ISO 8601 format, e.g. `2002-12-24 20:15:00'. Note that a date without time is treated as if the time is 00:00:00. Hence connections started on the specified date are consid- ered. -b, --before=DATE Only consider connections started before DATE. -u, --user=USER Only consider connections started by user USER. --this-year Only consider connections started this year. --last-year Only consider connections started last year. --this-month Only consider connections started this month. --last-month Only consider connections started last month. --this-week Only consider connections started this week. --last-week Only consider connections started last week. --today Only consider connections started today. --yesterday Only consider connections started yesterday. --per-year Show summaries on a per year basis. --per-month Show summaries on a per month basis. --per-week Show summaries on a per week basis. --per-day Show summaries on a per day basis. -c, --connections Only list the connections but do not show provider statistics. -p, --providers Do not list the connections only show the provider statistics. --connection-format=FORMAT The names of the columns to print in the connection list. The names must be separated by a comma. Known column names are time, type, provider, user, status, duration, rx, tx, rxtx, averx, avetx and averxtx. Each name may be appended by a colon and a number indicating the width for the column, e.g. provider:25. The value will not be truncated if the width is too small. --provider-format=FORMAT The names of the columns to print in the provider list equivalent to the --connection-format option. Known column names are provider, number, duration, rx, tx, rxtx, aveduration, averx, avetx and averxtx. --local Display all dates in local time. Also interpret dates on the command line in local time. This is the default. --utc Display all dates in UTC. Also interpret dates on the command line in UTC. --script[=STYLE] Format output for script. Known styles are c and sh. -f, --file=NAME Use a different file with accounting information. --report-errors Report erros in the accounting file. --version Show program version and exit. --help Show help information and exit. NOTES
All informations are only obtained through the accounting log. Hence the informations, esp. the data volume, of currently active connec- tions is not accurate. If you want the know the status of a connection, use ifstatus or a frontend to smpppd like cinternet , qinternet or kinternet instead. The user field is empty for connections started by a remote frontend of smpppd. BUGS
Not yet implemented for ISDN (ippp). FILES
/var/log/smpppd/accounting.log File containing accounting information. SEE ALSO
kinternet, qinternet, cinternet(1), smpppd(8), smpppd-ifcfg(8). AUTHOR
Arvin Schnell <arvin@suse.de> SuSE December 2002 ACCOUNTING(1)
All times are GMT -4. The time now is 07:05 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy