MYSQL :: Select Rows From Date Not Time In Where Clause

How To Select Distinct Rows Using The WHERE Clause
I have an app at work that will only allow me to customize the WHERE clause on a SQL statement.

Currently the SQL Statement is hard coded something like this:

SELECT * FROM `ERRORS`

Is there a way to do a DISTINCT in the WHERE clause that would return the same results as:

SELECT DISTINCT `MEMBER_NUMBER` FROM `ERRORS`

Select Date Only From Date Time Field
if I have "Date Time" field , How can I get the Date only from this field.

Select Date Ignoring Time
Im using a datetime fields eg. "2006-08-07 16:01:09" and I want to select dates... eg. "select * from mytable where mydate="2006-08-07" ignoring the time.

Select Date And Time In Same Column
I have table called mytable and there I have column named time
example:
time
2006-07-10 10:28:06
2006-08-18 20:48:22
2006-09-15 12:11:41
2006-10-12 23:06:02

is there any possibles that I can make query example:
SELECT *FROM mytable WHERE date BETWEEN 2006-07-10 AND 2006-09-15 AND time
BETWEEN 10:28:06 AND 12:11:41

My point is can I make query where I first find between date and after that I make
query where I find between time when information is in the same column?

First And Last Time In Each Date By Empl (was "Help Me With This Select Statement")
I have the following table which has the following fields:

logid, empid, transdate,transtime

I want to select the first and last transtime in a specific date for every employee, how can i do that?

PHP/MySQL Sorting By Date & Time (using Non Military Time)
This is probably a simple issue but I have searched online and can't find an answer.

I am using PHP/MySQL and I guess the most intuitive would be to have three select boxes containing HOUR / MINUTE / AM,PM option. Then store the time into mysql using there functions so I can output the data sorting them all by date and time. All the examples I have seen are for military time only but I'm sure there has got to be a simple mysql function or php function that converts non military to military and a formatting function to display with the AM / PM. But I have not found it.

Date-time Overlaps Another Time
had a time question in mysql...
i have a start and end time for a meeting table and i am comparing whether a new meeting conflicts with another meeting in same room on the same date.
i was wondering if i could check whether the 'date-time duration' in anyway overlaps another meetings date-time duration. visualized below...
............|____compareToThisMeeting__|
.....|_________meetingNewOverlaps__________|
i was doing something dumb before i realized i needed soemthing more complex (checking whether the start time of new meeting was 'BETWEEN' the start and end times of a meeting and whether the end time time is as well but i forgot the duration/middle value hehe).
(using asp)
and just returning booleans with a mySQL SELECT statement is what i am looking for like...
//within a loop in asp for meetings on same date/room
"SELECT '"+ year+"-"+month +"-"+day +" " + startHourString +":" + startMinuteString +":00''"+ " BETWEEN '"+ thisMeetingStart +"' AND '"+ thisMeetingEnd +"'"; (this doesnt cover the 'middle' overlap areas though)

Date/time Field Update Only Date
I have a field(tmMessage) in a table, it's a date/time struct.
it looks like "2003-09-21 15:52:35"

All I want to do is change the date in this field.
I want the time to stay the same.

Date Field, Time Field, Sort By Date And Time
I have a MySQL date field (e.g 13/12/2006) and a MySQL time field (e.g.13:00) in the same row.

I'd like to join these two fields to make a date/time field (e.g. 13:00 13/12/2006) on the fly and to be able to sort the query results on the resulting field.

Date Format And WHERE Clause
I have a question about a fiedl that I have in my table that has a date/time datatype.

DOS is the date/time field

I am trying to use this sql in Coldfusuion:

SELECT * FROM Shows WHERE Shows.DOS = '#Form.GigDate#'
ORDER BY DOS ASC

I keep getting a datatype mismatch error.

My date field has a Short Date format and I wondered what I am doing wrong in my sql and i believe it is in the WHERE clause.

Do I need something around the DOS part of that sql?

Determine The Size Of A Set Of Rows Based On A Where Clause
I have tried looking for a solution to this problem but no luck. The
thing is, in my system, I have a set of users who have a 'quota'
limiting their usage. They can create n number of tables and put m
number of rows in it, but at the end, their 'usage' can not exceed,
lets say, x MB. My problem is, how to I evaluate the space being used,
given the fact that I can pull all data for a user by including a user
ID in the where clause. In other words, I need to find size of the
result set containing all user's rows.

I am using mysql jdbc driver and not sure if there
is any API that deals with the physical size of the resultset.
resultset.getFetchSize() talks in terms of rows v/s the actual space
they take.

What Query To Check If Any Rows Exist Satisfying WHERE Clause?
I'm looking for a query that will check if any rows exists in a table according to a WHERE condition. I know I can use COUNT(*) but then mysql will do unnecessary task of counting all the rows whereas I just need true or false. So far I did this:

SELECT COUNT(*) AS exists FROM mytable WHERE ...
Sometimes I just select the first row and check later in php how many rows have been returned:

SELECT some_col FROM mytable WHERE ... LIMIT 1
But I cannot do this check (or can I?) in sql alone and I have problems when I want to use this in a subquery, for example:

SELECT id,
name,
(SELECT COUNT(*) FROM mytable WHERE ...) AS exists
FROM othertable
WHERE surname='xxx'
Can I do the same without using COUNT(*)? I would like a query that returns 0 or NULL if no rows were found, or 1 (or some other value) if 1 or more rows were found.

Select Clause
We have a recordset with the following structure id (int), mls
(varchar)

What we need to do is within a single sql call we need to show all
records where the mls = '' and all unique records where mls!=''

In other words, there will be multiple records with duplicate mls
entries. We do not want to show duplicate enties for where the mls has
been set, only where the mls has not been set.

Any suggestions?

SELECT Using IN Clause
for example, i'll do something like:

select first_name FROM users WHERE user_ID IN (2,1,3)

and i want the first name for user_ID 2 to come first, then 1, then 3
but instead, understandably so, it searchs 1, then 2, then 3, and i was severely hoping there was a way to preserve the order.

IN Clause For Select Query
Can someone help with query syntax regarding IN/EXISTS..

I'm trying to do this:

insert into table2 (field1) select field1 from table1 where field1 not in
(select field1 from table2)
delete from table1 where field1 in (select field1 from table2)

1. Insert field1 from table1 into table2 if it doesn't already exist
there...
2. Once moved, delete from table1.

Any help would be greatly appreciated.

Select Clause Problem
how can i select second highest age from a table having fields.

Select Statement Where Clause
Does MySQL support "==" in the where clause, or is an exact match the default behavior?
I noticed the following select statement in existing code:
***********************************
$q = "SELECT
account_id, account_username, account_email, account_password, account_status
FROM account i
WHERE (upper(account_username) = $tcName)";
***********************************
I would have expected two records matching for the following situation:
$tcName = "BECK",
with a record having account_username = "BECK" and
a record having account_username = "BECKER"

It only returned a single record ("BECK"). The account_username is a varchar - does that make a difference? Are there any MySQL settings which control "exact on"?

Where Clause In Select Query
I have this query atm: SELECT postid, body, posttime, commentcount FROM nz_posts WHERE sections = ':7:' ORDER BY 'posttime' DESC LIMIT 0, 5

this works, but "sections" can have something like ":7: :8:", ":4: :7:", etc... all the numbers are between : and there can be one or many numbers (all diferent).

I want to select all the rows that has :7: in the sections field.

Subquery In The SELECT Clause
I am having the problem in the sql statement

SELECT name,
(SELECT Sum(xxx) FROM myTable GROUP BY fld) as mySum ,
(mySum + 1)/2 as myVal
FROM myTable1

This resulted in an error!!
How can i use the 'mySum' in this query

Date And Time
I am having so much trouble formatting and checking against date and time; I think my query is simple this time. The following query does not work for me:

Select ........ from ..... where ... and h_date = curdate() AND s_time > now();

I am just trying to check columns in tables where hire date is today and start time is greater than now.

Please note, h_date is of DATE data type (Default) i.e. (yyyy-mm-dd)
and s_time is of TIME data type.

Date And Time
can someone explain me the avantage of using date and time, and also can =
i set time + XX minutes??

UTC Date/time
A data file I am trying to import has the date/time field in UTC format:

YYYYMMDD HHMMSS+0.

When I define my column field as datetime and import I only see zeros in all positions.

Can MySQL import data that are in the UTC format and if so what data type or command should I use?

SQL Date Time
I've been having trouble trying to figure out the best way to select only the rows with timestamps within the last X amount of hours, days, weeks, months.

So I guess my SQL statement in plain english would be:

Give me all the rows that have a timestamp between NOW and X days/months ago and count how many times a row was submitted per hour/day/week/month within those rows.

I'm pretty sure this is possible but I can't seem to wrap my brain around it.

About Date And Time
i have created a table using the following code:
------------------------------------------------------------------------------
create table tt(name varchar(10), enddatetime timestamp, startdatetime timestamp, difference timestamps);
------------------------------------------------------------------------------

after i have created, i insert the following into it:
------------------------------------------------------------------------------
insert into tt values('bob',null,'2006-10-1 12:12:12',null);
------------------------------------------------------------------------------

and i want to find the difference between the enddatetime and the startdatetime. so i tried the following:
-----------------------------------------------------------------------------
select enddatetime,startdatetime, sec_to_time(unix_timestamp(enddatetime)-unix_timestamp(startdatetime)) as difference from tt;
------------------------------------------------------------------------------

but the value came out from the difference column was xxxxx:xx:xx *(where x is an integer). as you can see, the year,month,date and the hour are all mix up together(xxxxx). so i was wondering whether my code or my variable was wrong?? and by the way, how can i show the difference in terms of seconds?? like converting the enddatetime and the startdatetime into seconds and substract them together?

After Date And After Time
I would like to make a memo script that once you have entered the date and time and note in a memo intto the database the memo is displayed once both date and time have passed. I dont want the memo to disappear until it is told to. I have an idea how to make it disappear, but I dont know what the syntax is for the sql for AFTER the date has passsed and AFTER the time has passed.

Time AND Date
i want to store the time in my field typ time.
i DON`T want to store date and time in one field.
in the field i see the time with an admin tool, but when i want to put out the value in an ASP page i get time AND date .how can i configure the field typ to store ONLY the time ?

Date Time
what I want to do is very simple however it seems I'm having trouble figuring out how :D
I got a date_time field in a table. I want to retreive all the records that match yesteday. Something like: select * from table where datetimefield = now() - interval 1 day
HOWEVER because it's a date_time field (and I can't split it in 2 fields for other reasons), the above query won't return anything. Seems I could do a select * from table where datetimefield between yesterday at 00:00:01 and yesterday at 23:59:59 but I can't find the functions for that.

Date & Time
i want to ast if i insert into table the value of now() in a datetime formated column does tha value it inserts if from my pc or from the server?

Inserting Text To All Rows At A Time
I need this very quick, sorry if this is answered already in this forum.

I need to insert some text at the beginning of all rows in a database,

View Reocords (rows) One At A Time
I am working on a PHP script where a data grid is shown; from here, user can select a row (or record) which is then opened in a window.

Once in the window, user can edit and save the record.  Saving the record barely flashes and shows message saying that record has been successfully changed.

I want to give the user the option to click on a NEXT button to move to next record in the table or PREVIOUS to move to previous record in the table while in "full record view mode".

Here is the catch, the data grid holds the recordID which is used to open record by windowed script.  On the grid, the records are sorted by NAME and so when pressing NEXT and/or PREV, I need to go to the record corresponding to the alphabetical order not the ID.

To make matter worst, the NAME is not UNIQUE.

My initial idea is to query by name where id equals ID start x,y but I get the feeling that this is not right, I just cannot put my finger on it.

Insert 4096 Rows At A Time
I need to increase a fair amount of rows to a mysql table (3.5 million to be exact). I'm doing that with a php script, but for some reason I can only insert a maximum of 4096 rows at a time. Anyone has an idea why?

Subtract Date And Time
is there a way that I can subtract date/time in MySQL?

For example I have this dates and I want to know the number of hours between this dates:

I want to perform 2006-12-05 22:00 - 2006-12-07 08:00. Can this be possible in a mysql query?

$date1= '2006-12-05 22:00';
$date2= '2006-12-07 08:00';

Date And Time Functions
I have been having problems using date and time functions in PHP. I am trying to design a schedule with a Calendar. I tried to use the following line of code to output the current month i.e. February. The line of code I have used is this :

$month = mysql_query("select monthname(curdate( ))");

I am getting the following output from this:

Resource id#6

why I am getting this output and are there alternative ways of getting the information I need.

Time Left Until A Specified Date
Using MySQL and php I need to be able to return the number of days, hours, mins and secs from the current date and time to a stored date and time in a table. Is there an easy way of doing this?

Date/time Function
I need to write a query which looks for certain criteria, then returns all rows which occur 0-30 seconds after. Can you please help with a generic query to accomplish this?

Created Date/Time
I would like to keep a row in my record that contains information on when the record was created and one when the record was last updated. What is the easiest way to go about doing this in MySQL?

Need To Include Time With The Date
I have a table called 'date_accessed' ...

When I query that table for the date, how do I include the time along with it?

Here is my query:

SELECT user_agent, ipaddress, date_accessed FROM access_tracker;

Date/Time Format From CSV
I have a rather large csv file that includes many dates that I would like to import into MySQL. The problem that I am having is that the dates are DD/MM/YYYY HH:MM:SS, and MySQL does not accept them in its YYYY-MM-DD HH:MM:SS format. Any ideas how to convert this properly?

Date/Time Issue
I am very new to MySQL. I have used other databases before so I am trying to figure out how to do things I have done before.

I want to create a table with a column of type DATE. When a row is inserted in the column I want the date the transaction took place to be inserted as the default in the column. I have tried a couple of things, but everytime I execute anything like:

SELECT CURDAT ();

MySQL reports Error 1146 Table mysql.proc doesn't exist. What have I not setup correctly?

Same thing happens when I execute:

SELECT VERSION ();

I am using version max-5.0.18 on a Linux system.

Sort Date And Time
I have a simple question about sorting records based on seperate date and time fields.

Take the following example query:

SELECT some_id FROM datatable WHERE date >= '2007-01-01' and time >= '05:00:00'

This returns all of the records with a date greater than 2007-01-01 and then reduces the records to only those with a time greater than 5:00. I want all records after 2007-01-01 5:00.

Date Time Question
first post:

Hello,
I'm trying to show what users are currently logged in on my site. what I've done is update a timestamp everytime they hit a page. what i want to do is select users where the current time Now() is within 15 minutes of the timestamp, to see if any users are on.

but I'm not sure how to do this.
any ideas? examples would be grate, I haven't done any asp in a long time.

thanks in advance for you time!
-Ken

second post:

ok, i've tried this,

SQL = "Select user from accounts WHERE DateDiff(n, Now(), date) < 15 ORDER BY user"

but i'm getting an error:

ADODB.Recordset.1 error '80004005'

SQLState: 42000
Native Error Code: 1064
[TCX][MyODBC]You have an error in your SQL syntax near '(n, Now(), date) < 15 ORDER BY user' at line 1

/admin/date.asp, line 6

also i'm using MySQL as the data base.

ORDER BY Date And Time
I am using php to extract variables from a MYSQL database that get loaded into a calendar application. Most everything works great. The only problem occurs when more than one event occurs on the same day. The event date and time are stored as separate variables in the database, thereby making it difficult to ORDER BY date and time. The result is that all of my events appear on the proper day, but they do not appear in chronological order. Is there a way to order by date and time?

I currently have the following:

Code:

$SQL_Query = "SELECT eventID, eventDate, startTime, endTime, title,
description, linkName, hyperlink FROM events ORDER BY eventDate";

Mysql Date Time
This is probably an easy one but i cannot seem to solve it, what I have is a database with a lastlogin datetime field in the form:
2005-01-17 00:14:30

I need to do a select on each entry to find the number of rows later than the date of the current row datetime, as I have users that log log in at different times.
I tried something like in a loop but this does not work

select count(*) as total from user_table where (NOW()>DATE_ADD(LastLogin, INTERVAL 0 DAY)) and iUserId != '$iUserId'

Conditionals With Date/time...
I am trying to figure out how to use conditionals with date/time using PHP with MySQL. For instance i want a time limit on my site to restrict people clicking a link within so many minutes.

So for example if they click the link once thats fine, but if they do it again say within the next 5 minutes i want one output, but if they wait the alotted 5 mins then another output.

Server Date Time
I have mysql server running in one machine. I have a query in my program
'SELECT CURRENT_TIME', it will retreive the system time.

I installed my program in the client machine and it is connecting to the mysql server. If i execute the SELECT CURRENT_TIME, it is giving the client machine date and time.
How can i retreive the server machine means on which mysql running, its date and time from the client machines.

Time And Date Fields
I have a form which insert data into a MYSQL table. In this MYSQL table I have a field called "DateEntered". Is it possible to have this field automatically updated with the current date when the form is submitted. What do I need to do in Mysql or on the html form?

Select Rows From Date Not Time In Where Clause

I am trying to select all the rows that where entered today and I have a datetime type column to store the date data in. How can I specify in my where clause to select where datecol=today and not have to specify the time? If I do where datecol=<?php date('Y-m-d'); ?> its not working