Most Efficient Date Field Type For Selects?
I have searched this forum seeking the answer to this question, to no avail.
I am making an events diary that will have a lot of entries. Entries will be entered in all kinds of order, including multiple events spanning a year or so.
Even multiple events of the same type will be individual entries. (e.g. weekly chess club meeting every weds at 7pm will be 52 entries)
My question is : Which is the best format to store dates?
Native mysql date type
Integer timestamp
A.n.other?
I suppose a worst case scenario is imagine I want to generate a calendar for July (31 days) and I want to show how many events there are for each day.
1st July (13 events)
2nd July (22 events)
and so on ... it is possible that any given day could have up to 100 entries.
What in your opinion is the best column type to use and ss there any kind of Indexing I can use to speed up select by dates? Or any other tip or trick you'd be prepared to share with me?
View Complete Forum Thread with Replies
Related Forum Messages:
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 !
I Need To Convert Date Type To String Type
I need to retrieve a Date type data from the database and present the data in my GUI as a String type. I've tried like this : ResultSet rs; Date data; data = (String)rs.getDate("ColumnName"); gui_label.setText(data); but they say inconvertible types... Can someone teach me the way so that I can retrieve Date type data from the database and present it in the GUI as a String type.
View Replies !
Passing Date Via Form For Mysql Date Type
I've created a database for tracking our paper inventory. Basically when an individual takes paper, or envelopes the quantity is entered into the database, along with some other items. This all works great. I also have two fields that use the "Date" and "Time" types for holding the date and time of the initial transaction. I've created a seperate php script that we will use for our "end of month" reports. The script goes through and adds up the cost of each "purchase" between a specified time frame (1 may thru 31 may for example). This script works for me as long as I perform my query with my condition formated as such:
View Replies !
Does PK Field Type Have To Be Same As FK Filed Type?
I have a table with a primary key and, in another table, a foreign key to that 1st table needs to be set. should both fields have the same type i.e. where the PK is a 'int(11)' should the FK field be 'int' as well. And if so does it neeed to be limited to 11 as well?
View Replies !
Field Type :: Time Field With Decimal
I want to add times into a field in the form mm:ss.d such as 03:33.5 but when I use the time type it removes the decimal but I really need it to keep it in. At the moment I am using a char(10) type and converting it in my scripts. Is there a way I can add it in using the correct type? I couldn't see anything in the documentation on the time type except that you could enter it in the form I want but it will convert it.
View Replies !
Date Type Default Value
I would to give the CURDATE() value to a date field as null value. I wrote: sql_fecha` date NOT NULL default 'CURDATE()', and I get : Error #1067 - Invalid default value for sql_fecha I take off the () and got the same result. I also cast the expression as : CAST( CURDATE() AS DATE) and the same error.
View Replies !
Change Format Of DATE Type
The DATE type has the format YYYY-MM-DD. Just wondering why it is like this, and not DD-MM-YYYY (or MM-DD-YYYY) as people normally write dates? And more importantly, how do I change dates which have been entered into a form in the "human" format (MM-DD-YYYY) into the format required by MySQL?
View Replies !
Compare Date Type Column
is there anyway i can compare 3 or more date type column? let's say i have ------------------------------------------------ date1 | date2 | date3 ------------------------------------------------ 2006-06-01 | 2006-06-15 | 2006-09-19 ------------------------------------------------ can i just get the earliest date by command line? select XXXXX(date1,date2,date3) <--returns '2006-06-01 ' select YYYYY(date1,date2,date3) <--returns '2006-09-19 '
View Replies !
Using DATE_ADD With Date Type Column
MySQL Version - 4.0.17 Is it possible to use DATE_ADD with a "date" type column? I need to select all of the records that are exactly 11 months old... I have the following: SELECT fname, sname from Calendar_Appointment_Dates Where DATE_ADD(appt_date, INTERVAL 11 MONTH) = CURRENT_DATE() But this doesnt seem to return any records, despite the tbl containing nearly 6000 rows......
View Replies !
Field Type
i create a table: mysql>create table a(a float); mysql>insert into a values('39923.56'); mysql> select * from a; +---------+ | a | +---------+ | 39923.6 | +---------+ 1 row in set (0.00 sec) ////////////////////////////// i do not want the round result! i want get the values like *.?? i do not want use the DECIMAL(5,2) because it can not caculated by the cpu. one way maybe i can use bigint then /100 get the float num! any other ways?
View Replies !
Type Field
At this moment I have a field with the type DATE using by default 0000-00-00. I want modify to correspond to the next example: 2006-06-06 15:30 PM. Can I register in the same field or I want to separate by other fields?
View Replies !
What Field Type
I'm using a credit system in my database what is the best field type to use for numbers which will have values added and deducted.
View Replies !
DATE Data Type For Athletics Timings
Is there a DATE data type that I can use in a database of athletics performance times in the format hours:minutes:seconds.tenths of a second e.g 10:22:12.77 If there is a data type I can use, how would you truncate the format of the data type to record times for the 100 metres, e.g. 10.31 seconds. Please bear in mind I am a complete novice at mysql.
View Replies !
How To Convert A Varchar Field Into Proper Mysql Date Field?
I have a database in which the date is stored in varchar field in a following format: d-m-Y (06-08-2007), now the problem is that I want to change that field into mySQL date field as well as convert my older dates into MySQL date format i-e Y-m-d (2007-08-06).. There are about 300 old entries..is there a way I can do that automatically without manually re-entering the dates again?
View Replies !
TEXT Field Type
Is there a way to limit the field type TEXT to only store up to 1000 chars? or is it something that you can limit in your script when storing data?
View Replies !
TIME Field Type
Within PHP I have the time data of 01:32 or 1:32 - this refers to a mm:ss / m:ss format. My MySQL Field type is a TIME field - I require it to be a time format so I can perform functions upon the row. The time format is 00:00:00 (hh:mm:ss) - is there any way of having the field correspond with my format, or is there a way of converting it upon entry. If not it means I'll have to use string functions in PHP to convert the data into the neccessary format - which frankly is a pain the ***. Then again, I'm confident there's a solution available in MySQL, and I'm uber-confident that you guys will be the people to ask too.
View Replies !
Year Field Type
My question is, how do I prevent default data being entered into this field when I don't want to enter a year for the given row?
View Replies !
Speed Vs Field Type/value
I have an integer field in a table or view, and that field is used in the WHERE clause of a SELECT statement. Will the query take longer if the field values are in the upper range of the max vs. the lower range ( 200000000 vs 1, 2, 3), or is a 32 bit integer queried at the same speed regardless of the value?
View Replies !
Which Field Type Question...
I was wondering which column type to use to contain a list of categories (1 cat per row). If the data is probably not going to be longer than about 10 characters for each item would varchar(10) be sufficient or should I include a larger number? How much space do you normally give yourself? Or would char be better?
View Replies !
What Data Type For Currency Field?
I am on MySQL 4.1.20, and need to create a table that will have fields containing US currency amounts, up to $999,999,999.99. My boss wants a webpage displaying the table, and he wants the fields to be formatted to have a dollar sign, commas, and decimals. Am I better off making the fields varchar so the $ and , can be added -- or should I make the MySQL fields as float or decimal and then format the display on the webpage with (PHP, Javascript, ?)?
View Replies !
Converting Field Type In Active Database
When I designed my db in several tables I used unsigned integer fields to hold an integer that each bit was a 'flag'. Now I realize that the set field type does just what I was trying to accomplish. Is it safe to convert the field(s) from uint to set and keep their current values? Obviously all external programming (queries) will have to be adjusted, but it seems to make more sense to use the set type.
View Replies !
Problem Updating Time Type Field
Here's a query I tried and doesn't work for me. UPDATE activities SET ljTime=19:00:00 WHERE actID=05052003 I have also tried this: UPDATE activities SET ljTime=07:00:00 [PM] WHERE actID=05052003 But I still receive the error: [txoov] ERROR 1064: You have an error in your SQL syntax near ':00:00 WHERE actID=05052003' at line 1.
View Replies !
Field Type To Store Text With Commas
I need to change my field type to allow for commas in the field name. So I tried to change my text from "varchar" to "text". However, I am getting this error: #1170 - BLOB/TEXT column 'title' used in key specification without a key length Can anybody tell me if text is the right type to use? If so why isn't my database allowing me to make this change?
View Replies !
What Field Type To Use To Avoid Blank Spaces In Fields
Can you tell me the best field type to use here? I've got a table in mysql with all 5 fields defined as tinytext Problem is when I export this to to a text file for notepad each field is padded out by several blank spaces, and i think my eamil program doesnt like this type of structue : field1 , field2 , field3 , field4
View Replies !
Date Range On Date Field
I have a table that has birthday dates stored as a DATE field. How would I get all the rows returned based on a min years old and a max years old...Let's say... My form has.... Display Rows by birthdate in years.... Show between (23 years old) AND (36 years old) from the current month/day/year
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 !
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 !
Date Field
I have a table with a date field, stored I believe as yyyy-mm-dd. I want to pass a date value to a page and match records based on that particular date. I've tried this: SL.seminar_date = '" & request("date") & "'" and several variatons of the same but keep getting an error.
View Replies !
DATE Field....
i have a date field, that my users enter for when they captured a lead... field name type Lead_Date DATE and the format is yyyy-mm-dd i then have a page that calls the entire table for my agents to view... but if they did not happen to enter a date into the Lead_Date field, it shows: 0000-00-00 how can i just not have it show, or show blank?
View Replies !
Blank Date Field
How do you determine if a Date field is blank. In my Sql statement I only want records who's date field is blank (i.e. checking for only open invoices where Date_Paid = '' ...still open ...it doesn't work when I use that syntax)
View Replies !
NULL For Date Field
I have a date field in my database which is set to accept NULLs. If I enter a record from phpMyAdmin and leave the date field blank, the record is entered properly. However, if I do the same thing from a form, I get an error that the date cannot be blank. It seems that I need to enter a NULL in the date field from the form rather that nothing.
View Replies !
0000-00-00 In 'Date' Field ?
I have a field in a table called 'ShipDate'. This particular field has allow null set to no. So by default this sets all the records to show 00/00/0000 in the field for each record that has no data entered. In my .asp pages I am trying to test against this empty field. i.e. If RS("ShipDate") = 0000-00-00 or If RS("ShipDate") = 00/00/0000 The only thing that seems to work is if I am testing for records with a date in that field, If RS("ShipDate") > 0000-00-00 How can I test if that field is empty ?, nothing seems to work ?
View Replies !
Date Field Null Value
I have a date field set as default="NULL". I think that is causing me problems when I try to see if that date field contains a value. The mysql below only works when I get rid of the date field AND charged='NULL'. How can I tell if a date contains a NULL value? "SELECT * FROM orders WHERE enterdate >= '$fromsql' AND enterdate <= '$tosql' AND orderstatus='CLOSED' AND charged='NULL'
View Replies !
Default Value For A Date Field
I am trying to create a column inside a preexisting table. The column has a type of date. Is there a way to set the default value to now() or curdate()? In MSSQL the default value would be 'getdate()', but when i try to use the MySQL varient an error returns saying this is an invalid default value.
View Replies !
|
TRACKER
