Combining Date And Time Fields Into A Datetime Field
I have a table with separate Date and Time fields.
Is there a way to either:
1. Convert the date and time fields into a combined datetime field?
or
2. Create a datetime value in the select statement so I can select all records before a given date and time?
View Complete Forum Thread with Replies
Related Forum Messages:
Datetime Field Updateing Time Only
How would I update the time only on a datetime field? The filed is: "2008-09-14 12:06:00" and I want to change it to "2008-09-14 12:16:00". If I use  update field_name set field_name = TIMEDIFF('2008-09-14 12:06:00', '2008-09-14 12:16:00');Â
View Replies !
Update Time Ony In Datetime Field
How do I update the time portion in a datetime type field? There are several rows containing various times and I want to make them all the same: 11:30:00. I have tried doing the following w/o success: update field set field = "date(field) 02:02:00"
View Replies !
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.
View Replies !
Creating Datetime Data Object From Date And Time Objects
I have date and time objects that correspond to eachother. I want to add a certain amount of minutes and hours to them and get the resulting date and time. I was planning on creating a datetime datatype from the two and then using the date_add() function and then convert back; however, I do not know how to create a datetime object from two seperate date and time types and then back to seperate types, or whether it is even possible.
View Replies !
Comparing DATETIME Field With Date...
I'd like to make query which compares two dates - one from database, ang second in php (date("Y-m-d H:i:s")). I'd like to make query which gives me back those rows where the date from db is more than three days older than current date.
View Replies !
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?
View Replies !
Indexing Date / Time Fields.
If we have a date/time field and are doing frequent queries WHERE {date/time field} BETWEEN TimeA AND TimeB Does it make sense, query speed wise, to create an index on the date/time field? The reason I ask is that TimeA and TimeB are significant down to seconds. My *assumption* is that with a large amount of data scatter very few records will contain duplicate date/time points. And that under these circumstances, indexing will be of little value since every date/time point will need to be examined in detail. Date/time fields are actually floating point numbers after all. In other words, the assumption I am making is that if most of the data in a field is unique, then indexing will yield little or no query performance benefit. Is my "assumption" valid? Would an index on the date/time field speed up this type of query?
View Replies !
Creating Date And Time Fields
What's the correct way of creating date and time fields. Do I set a default for these fields of NULL so I may query NULL values instead of '0000-00-00' values? What's the preferred way?
View Replies !
Date/time Field Type
I want a field to automatically record the date/time each time a record is written to the db. Do I want that field to be of type DATE, DATETIME, TIMESTAMP…? My book is less than clear.
View Replies !
Generating DAILY Min&Max Over A Period Of Time, Based On A "DateTime" Field
how to print the minimum and maximum of a specific field, between two datetimes (both having the time 02:00:00) - so I did this: WHERE ((tblvalues.dtLPDateTime)>="2006-01-01 02:00:00" And (tblvalues.dtLPDateTime)<"2006-01-03 02:00:00")) Now I want to print those min and max values but for each day in this cycle, i mean, for this example, there would be two records like: Day Min Max 2006-01-01 02:00:00 -> 2006-01-02 02:00:00 | 40 | 59 2006-01-02 02:00:00 -> 2006-01-03 02:00:00 | 49 | 68 where at this point i`m getting min=40 and max=68
View Replies !
Date Field, Time Field
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) and to be able to sort the query results on the resulting field.
View Replies !
Adding A Date Field, Need To Populate The Fields
I have a table and until now there is was date entry for the fields. I have added a timestamp (column "time") and from now on, every time I insert a new entry, the date/time will be recorded. The thing is, I want a date/time to exist for the old fields. I have about 250 fields and I want each field to be assigned a timestamp based on the fields alphabetical order (ordered by "title"). So, field with title "aaaaa title" will have a timestamp of today. A field with a title "zzzz title" will have a timestamp of 250 days ago. It doesn't have to be exactly like this. My main goal is to assign dates to my old fields. Anyone know how to do this?
View Replies !
Selecting Date Time Field, Difference In Minutes
I wonder if is there any way to select from a table all the records which has a Date Field that is at least five minutes old? In other words, I have a table with a date field and I need to select all the records that are older than five minutes, has their date field updated before five minutes. Usually I do it with days by using the to_days() function and comparing the date to now: (to_dayss(now()) - to_days(somedate)).
View Replies !
Combining Fields
I'd like to add a new column/field to an already existing table, and move into this field the content of several other fields, separated with comma. Can anyone tell me if this ss this possible? And if so, what the syntax would be like?
View Replies !
Compare Datetime Field With Todays Datetime
How do I compare a datetime field in the database with todays datetime... Is it: if rs("fieldname")<>getdate() then .... else (gotto set a cookie here) end if Somebody suggested I use cdate...what is tht for..is it to make sure they are both in the same format or something.. if so how do I use it?
View Replies !
Combining UPDATE Statements With Different Fields And Conditions
I'm slightly paranoid that I haven't unearthed an existing answer to this question, but the ones I have been able to find didn't have two different variables requiring two different conditions. Basically, I want to know if it's possible to combine the following UPDATE statements into a single query: UPDATE table SET field1 = field1 - n WHERE field 1 > x UPDATE table SET field2 = field2 - n WHERE field 2 > x .......
View Replies !
DateTime Without Time
I have a difficult case and need your advice: For managing events in my online application I need to be able to input the start and end. They can be inserted either as a date only or as a date and time both. And I save this data into the DATETIME format. The problem is, if only a date is inserted, MYSQL will put the missing time information automatically as '00:00:00' but I don't want this. Because it will display the time as '00:00:00' which is wrong. Is there no way to save into DATETIME format without the time?
View Replies !
DATETIME Fields
Why can i no longer update my datetime fields? SELECT * FROM x200806 WHERE MONTH(stamp)<>'6' # =755 records UPDATE x200806 SET DAY(stamp)=1, MONTH(stamp)=6 WHERE MONTH(stamp)<>'6' # syntax error (likewise if i say '1' instead of 1) UPDATE x200806 SET DAY(stamp)='1', MONTH(stamp)='6' WHERE DAY(stamp)='30' AND MONTH(stamp)='5' # syntax error UPDATE x200806 SET DATE(stamp)=STR_TO_DATE('1/6/2008','%d/%m/%Y') WHERE DAY(stamp)='30' AND MONTH(stamp)='5' # syntax error UPDATE x200806 SET stamp=STR_TO_DATE('1/6/2008','%d/%m/%Y') WHERE DAY(stamp)='30' AND MONTH(stamp)='5' # Duplicate entry '2008-06-01 00:00:00-928897152' for key 2 But i dont want the time part changed to Zero !! I used to be able to say SET DAY(stamp)=1 but this doesnt work any more.
View Replies !
Comparing Datetime And Time
I need to compare two values : the first one is in DATETIME format and the second one in TIME format. If I compare the two values with an usual operator, will MySQL extract the "time" part of the DATETIME and compare it to the TIME value? If not, what function can I use to extract the "time" value? I have noticed the existence of TIME(expr) function, that extracts the TIME value from 'expr'... but it works only if 'expr' is a string, not a DATETIME.
View Replies !
Combining Multiple Records Into One Based On A Key Field
I'm really scratching my head over this one. I'm working with CSV data exported from Excel, which explains why it's a mess to begin with. Within a table (or via any other means someone might be able to recommend) I need to combine multiple records which share two like fields. (If that's not clear, the real-world explanation is below.....
View Replies !
Help With Selection Of Datetime Fields
I'm having some trouble with certain SELECT statements that I need to execute. Basically I have a field of DATETIME format, and I need to be able to do things like: Select where <mydatetimefield> is between now and 15 minutes ago. Select where <mydatetimefield> is between now and 1 day ago. Select where <mydatetimefield> is between now and 1 hour ago. Can anyone point me in the general direction of some examples of this sort of thing?
View Replies !
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.
View Replies !
Elapsed Time Between 2 DateTime Values
MySQL Version 4.0.20 on a Linux server. How does one get the elapsed time between (2) DateTime values? I need the answer to the nearest minute. Is upgrading to Ver 5 with its more robust date/time functions the only solution? You can directly subtract 2 DateTime values and a long integer results. What is that number?
View Replies !
Update Just Time In Datetime Column
I have a datetime column called foo, it currently has dates in it with all times set to "00:00:00". I would like sql to update all the rows and set the time to "15:15:00" but leave the dates as they are today.
View Replies !
Datetime :: 12 And 24 Hours Time Format
I am using webservice to download data from mySQL to SQL Server. I noticed in sql server datetime format makes the hour 12 to 00. for example 2008-06-12 00:40:47.000 This is really 2008-06-12 12:40:47.000 My question is how does mySQL treats 24 hours format. with 12 or 0 for the hour "12".
View Replies !
Difference In SELECT WHERE IN With Datetime Fields
Anyone can explain the following? Table definition: CREATE TABLE testdate ( SalesDate DATETIME, SalesTotal DOUBLE, PRIMARY KEY (SalesDate) ) If I use the following SQL statement I get results: SELECT * FROM testdate WHERE SalesDate = '2003-08-27' OR SalesDate='2003-07-27'; But I get an empty set when I use this: SELECT * FROM testdate WHERE SalesDate IN ('2003-08-27', '2003-07-27'); This happens on both 4.0.15 and 3.23.57. But the above statements work the same under Microsoft SQL Server. Why is MySQL interpreting WHERE IN and WHERE = OR = differently?
View Replies !
Datetime :: Start Date And End Date
I have a to generate an hourly report. I have a web page in which a user selects a customer, a product and time frame(startdate and enddate). the start date selected will be in these format 05/19/2005 and the end date will be these format. In the database i have a datetime column in these format. 2005-05-19 13:27:17.441043-04 so the question is how do i write a select query. and when i run mysql select query i should get all the record which fall under the given time frame. now i want to get a hourly record. means at which hours that records was created.
View Replies !
Function To Convert From DateTime To Relative Time
I have a DATETIME column and I looking to write a function which does the following: Takes in the DATETIME and compares it against the current time, and returns a string in any of the forms below, depending on the value of the DATETIME: 'You updated your settings 4 seconds ago.' 'You updated your settings 2 minutes ago.' 'You updated your settings 6 hours ago.' 'You updated your settings 12 days ago.' But its smart and automatically scales from seconds, to minutes, to hours, to days, rounding either up or down.
View Replies !
Subtract 2 Datetime Fields ? Get Remaining Days
would appreciate it if anyone can help me figure out to substract 2 datetime fields to get the days left difference. PHP Code: startdate=2008-04-13 14:03:11 enddate=2008-10-13 14:03:11 days-left field is an integer the difference is 180 days(6 months). so far without converting to a unix timestamp,I got :600000000 in the days left field and i dont know what the hell is that is(it certainly not 180 days in seconds-15552000. I would not mind converting datetime to unix timestamp if i can have the result inserted in the days-left field only i need to keep datetime in first 2 fields.
View Replies !
Group By Date, Count Of Multiple Fields Within Date
take table a: userID INT, userLogin VARCHAR and table b: customerID INT, userID INT, customerStarted DATE, customerFinished DATE what I'm looking to produce is a report by date, showing number of starts and finishes for all customers marked with a specific userID, like User 4: Date | starts | finishes 2008-10-01 | 0 | 5 2008-10-02 | 3 | 3 2008-10-03 | 4 | 2 2008-10-04 | 0 | 0 2008-10-05 | 3 | 3 ... etc.
View Replies !
DATETIME -> DATE
I have a table with a DATETIME column named 'date'. I find that my queries often do 'DATE(`date`)' and so I figure that it'd make more sense to have a column with the actual date in it, rather than the DATETIME. I could then make that column an index which would speed up my queries.Does that make sense?If so, how best to do this. Something like this? ALTER TABLE rawdata ADD daydate DATE after date, ADD INDEX (daydate); UPDATE rawdata SET daydate=DATE(date); It seems wasteful/etc to duplicate the actual date, but I can't remove the DATETIME column since it's already used in other applications. Is there some way to have a column whose value is automatically derived from another column?
View Replies !
Convert Int Date To Datetime
I have a table that unfortunatley has a field 'DateSold' with datatype of int that SHOULD be datetime. So, I am trying to do a cast/convert. The int is stored as 20040520 (which means 5/20/2004) I tried saying Select col1 from table where cast(DateSold as DATETIME)[color=blue] > '1/1/2004'[/color] but of course it bombs out. What is the most efficient way to do this? Is there a way to cast it without doing some crazy string manipulation?
View Replies !
UPDATE Date Of DATETIME
I have a datetime column. And for some random row of data, I want to change the date (but not the time) of the datetime column. How do I do this? before: datetime = 2006/04/03 12:30:25 if I do: UPDATE table SET DATE(datetime)='20060404' where id=whatever; I get this: datetime = 2006/04/04 00:00:00 I also tried: UPDATE table SET DATE(datetime)='20060404'+TIME(datetime) where id=whatever; but that seems to take the time and add it to the date instead of concatenate it or something. Anyways, my point is, I'm trying to update the date and only the date for a datetime value without adjusting the time. I know there is some simple way to do this but I'm far from a MYSQL syntax expert.
View Replies !
Date & Datetime Question
I'm trying to check for dates that are >= but with a Date field, it doesn't work with NOW()... exp_date >= NOW() How can i have mysql ignore the hours/min/sec and just compare date to date properly?
View Replies !
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)
View Replies !
DateTime Sorting - Put Empty Date To The End
is there a way to sort DateTime and put the empty DateTime (0000-00-00 00:00:00) to the end? Example: SELECT dat FROM blah ORDER by dat It shows me: 0000-00-00 00:00:00 2009-04-07 13:10:00 2009-04-08 13:10:00 but I want: 2009-04-07 13:10:00 2009-04-08 13:10:00 0000-00-00 00:00:00
View Replies !
|
TRACKER
