Re: Effect of VARCHAR length?
> I'm just curious to know if the length of the indexes on a varchar > column work in the same way or if they have a fixed lenght. > anybody knows ? I don't see how they could be fixed length, since VARCHAR itself is not fixed-length. Ergo, it makes sense that the prefix limitation is the upper prefix length limit. Reference: http://dev.mysql.com/doc/mysql/en/mysql-indexes.html http://dev.mysql.com/doc/mysql/en/create-index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Effect of VARCHAR length?
> I've just been wondering if the length parameter of a VARCHAR column has
> any effect on storage efficiency or space requirements. Afaik, VARCHAR
> columns only store the amount of data actually written into them and
> require no significantly more memory. So to be especially flexible with
> a particular table column, could I just define it VARCHAR(255) and face
> no further disadvantage of it?
mysql> CREATE TABLE vc (
-> vc1 VARCHAR(5),
-> vc2 VARCHAR(255)
-> );
Query OK, 0 rows affected (0.16 sec)
mysql> INSERT INTO vc (vc1, vc2) VALUES
-> ('this is a test', 'this is another, longer test');
Query OK, 1 row affected, 1 warning (0.06 sec)
mysql> SELECT * FROM vc;
+--+--+
| vc1 | vc2 |
+--+--+
| this | this is another, longer test |
+--+--+
1 row in set (0.00 sec)
>From this, we can see how defining the field as VARCHAR(5) limits the
maximum length to 5 characters; we can assume, too, that it will
likewise chop off any strings longer than 255 characters in vc2 the
same way. The length parameter simply provides the upper limit of the
string that might be stored in that field, useful in some instances,
irrelevant in others.
All VARCHARs/TINYTEXTs are stored with a single-byte length prefix,
regardless of how long you let them be (less than 256, of course),
plus the string it's storing. So, for maximum flexibility less than
256 characters, use VARCHAR(255) and don't worry about it.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: timediff and subtime: when is the result negativ?
This is not surprising behaviour. If you subtract 12 from 10, you get -2 every time. If you want your result to be positive, make it the first parameter and subtract the smaller (earlier) time from it. If your result is negative, then you know you have crossed into another day (13:00:00 - 17:00:00 = -04:00:00, meaning the 17:00:00 was from yesterday afternoon). If your times can be more than 24 hours apart, then working with SUBTIME won't help you much, by itself. Reference: http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html SUBTIME(expr,expr2) SUBTIME() subtracts expr2 from expr and returns the result. expr is a time or datetime expression, and expr2 is a time expression. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to write this query?
SELECT start_date, end_date, DATEDIFF(end_date, start_date) - (WEEK(end_date) - WEEK(start_date)) * 2 AS business_days FROM DateDiffs ORDER BY start_date; +-+-+---+ | start_date | end_date| business_days | +-+-+---+ | 2005-01-04 16:44:57 | 2005-01-10 17:53:33 | 4 | | 2005-01-17 00:00:00 | 2005-02-18 00:00:00 |24 | | 2005-01-21 00:00:00 | 2005-02-18 00:00:00 |20 | | 2005-01-24 00:00:00 | 2005-02-18 00:00:00 |19 | | 2005-01-28 00:00:00 | 2005-02-18 00:00:00 |15 | | 2005-01-31 00:00:00 | 2005-02-18 00:00:00 |14 | | 2005-02-04 00:00:00 | 2005-02-18 00:00:00 |10 | | 2005-02-07 00:00:00 | 2005-02-18 00:00:00 | 9 | | 2005-02-14 00:00:00 | 2005-02-18 00:00:00 | 4 | +-+-+---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question!
> I was wondering if you could tell me of three or four alternative software > options that can be used for this middle layer. What are the costs involved > with any of these. Also if you could supply me with a sample script for > inserting data using these alternative scripting languages, as I have been > implementing with PHP. Google for: PHP ASP JSP CGI ColdFusion I'm sure you'll find more than enough information there. PHP is free. ASP uses VBScript (usually), comes from Microsoft and is currently free too, as is ASP.NET. JSP uses Java, comes from Sun Microsystems and is probably free as well. CGI is free, written in C or Perl. ColdFusion is not free, is from Macromedia (the same company that does MM Flash/Fireworks/Freehand/UltraDev) and, like PHP, uses its own language. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to write this query?
On Sat, 19 Feb 2005 14:01:05 +, Jerry Swanson <[EMAIL PROTECTED]> wrote:
> I have two dates (start_date, end_date). Datediff() function returns
> difference in days.
> I need the difference but not including Satuday and Sunday.
>
> Any ideas?
C:\Program Files\MySQL\MySQL Server 4.1\bin>mysql -utest -ptest test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 43 to server version: 4.1.8-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `DateDiffs` (
-> start_date DATETIME,
-> end_date DATETIME
-> );
Query OK, 0 rows affected (0.15 sec)
mysql> INSERT INTO DateDiffs
-> (start_date, end_date)
-> VALUES
-> ('2005-02-14 00:00:00', '2005-02-18 00:00:00'),
-> ('2005-02-07 00:00:00', '2005-02-18 00:00:00'),
-> ('2005-02-04 00:00:00', '2005-02-18 00:00:00'),
-> ('2005-01-31 00:00:00', '2005-02-18 00:00:00'),
-> ('2005-01-28 00:00:00', '2005-02-18 00:00:00'),
-> ('2005-01-28 00:00:00', '2005-02-18 00:00:00'),
-> ('2005-01-24 00:00:00', '2005-02-18 00:00:00'),
-> ('2005-01-21 00:00:00', '2005-02-18 00:00:00'),
-> ('2005-01-17 00:00:00', '2005-02-18 00:00:00');
Query OK, 9 rows affected (0.06 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> SELECT
->start_date,
->end_date,
->datediff(end_date, start_date)
->AS dd1,
->datediff(end_date, start_date) -
floor(datediff(end_date, start_date) / 7) * 2
->AS dd2
-> FROM DateDiffs
-> ORDER BY start_date;
+-+-+--+--+
| start_date | end_date| dd1 | dd2 |
+-+-+--+--+
| 2005-01-17 00:00:00 | 2005-02-18 00:00:00 | 32 | 24 |
| 2005-01-21 00:00:00 | 2005-02-18 00:00:00 | 28 | 20 |
| 2005-01-24 00:00:00 | 2005-02-18 00:00:00 | 25 | 19 |
| 2005-01-28 00:00:00 | 2005-02-18 00:00:00 | 21 | 15 |
| 2005-01-28 00:00:00 | 2005-02-18 00:00:00 | 21 | 15 |
| 2005-01-31 00:00:00 | 2005-02-18 00:00:00 | 18 | 14 |
| 2005-02-04 00:00:00 | 2005-02-18 00:00:00 | 14 | 10 |
| 2005-02-07 00:00:00 | 2005-02-18 00:00:00 | 11 |9 |
| 2005-02-14 00:00:00 | 2005-02-18 00:00:00 |4 |4 |
+-+-+--+--+
9 rows in set (0.00 sec)
mysql> DROP TABLE DateDiffs;
Query OK, 0 rows affected (0.19 sec)
mysql> exit
The column dd1 contains the unaltered DATEDIFF() which includes the
Saturdays and Sundays, while the dd2 column contains the number of
business days omitting the weekend days.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select only values >0
> Dear All, from a single row of a table, I have to select only the column, > which have a value larger '0' into an outfile. > How can I manage it with 'select'? Thanks, Jan SELECT CASE can do that sort of thing for you. Here's a simplistic example: CREATE TABLE `test` ( `i1` int, `i2` int, `i3` int ); INSERT INTO `test` (`i2`) VALUES (2); SELECT CASE WHEN `i1` > 0 THEN 'Field 1' WHEN `i2` > 0 THEN 'Field 2' WHEN `i3` > 0 THEN 'Field 3' ELSE 'No match' END AS `iMatch` FROM `test`; +-+ | iMatch | +-+ | Field 2 | +-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
