mrkeck Sharing the Love

4May/170

MySQL Get Consecutive Dates for Date Range

Source - http://stackoverflow.com/questions/2157282/generate-days-from-date-range

Not exactly what I was looking for, but I found the technique very intriguing


select a.dt
from (
select '2018-01-01' - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as dt
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.dt between '2017-01-01' and '2017-05-21'
ORDER BY a.dt

Results:
2017-01-01
2017-01-02
2017-01-03
2017-01-04...
2017-05-20
2017-05-21

Filed under: mySQL No Comments
28Jun/160

mySQL Start of Week on any Day

Needed query to show start of weeks based on Wednesday

DATE_SUB(exampleTimestamp), INTERVAL ( ( DAYOFWEEK(exampleTimestamp) + (6-DAYyouWANT) ) % 7 ) DAY) startOfWeek

0 - Sunday
...
3 - Wednesday
...
6 - Saturday

Filed under: mySQL No Comments
9Oct/150

DELETE From Same MySQL Table

From http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql

Quoted in case the page goes away one day

DELETE FROM NAMES
WHERE id NOT IN (SELECT *
FROM (SELECT MIN(n.id)
FROM NAMES n
GROUP BY n.name) x)

I guess you have to sneak it past by putting inside another subquery. Worked!

Filed under: mySQL No Comments
3Sep/140

mySQL import dump file to RDS

mysql -h host.address.for.rds.server -u rdsusername -p rdsdatabase < backupfile.dump From http://stackoverflow.com/questions/11731714/how-do-i-import-a-local-mysql-db-to-rds-db-instance which is explains in more detail

Filed under: mySQL, RDS No Comments
11Sep/120

phpMyAdmin Change CSV Field Termination

To change phpmyadmin default import/export option settings find "config.inc.php" or "config.default.php" under the phpmyadmin folder and change this line

$cfg['Import']['csv_terminated'] = ',';

If it is not there add it.

Filed under: mySQL, phpMyAdmin, wamp No Comments
28Dec/110

mySQL ODBC Error

"The specified DSN contains an architecture mismatch between the Driver and Application"

I'm using WAMP on 64-bit Windows 7. Turns out when you go to ODBC connections through control panel that brings up the 64-bit connections. I had to go to c:\windows\sysWOW64\odbcad32.exe to setup the connection for 32-bit on a 64-bit OS.
Works great!

http://msdn.microsoft.com/en-us/library/windows/desktop/ms712362(v=vs.85).aspx

To manage a data source that connects to a 32-bit driver under 64-bit platform, use c:\windows\sysWOW64\odbcad32.exe. To manage a data source that connects to a 64-bit driver, use c:\windows\system32\odbcad32.exe. In Administrative Tools on a 64-bit Windows 8 operating system, there are icons for both the 32-bit and 64-bit ODBC Data Source Administrator dialog box.

Filed under: msaccess, mySQL, PDO, wamp No Comments
31Aug/110

Windows ODBC connect to MySQL

http://dev.mysql.com/downloads/connector/odbc/

After the download and setup, MySQL will be an option in Data Sources (ODBC) under Administrative Tools in Control Panel

Filed under: mySQL, windows No Comments
29Jun/110

MSSQL to MySQL SQL Commands

replace(convert(varchar, NOW(), 101),'/','') to date_format(NOW(),'%m%d%Y')  = 06292011

'E-'+cast(stub_id as varchar(15)) to concat('E-',stub_id) = E-12345

GETDATE() to NOW() = Current Timestamp

Note: MySql dates are in 2011-06-29 form

SELECT TOP 1 * from table1 to SELECT * from table1 LIMIT 1 = Return one row

I did a conversion recently and these 4 are the things I ran into the most.

Filed under: MSSQL, mySQL No Comments
14Jun/110

MySQL Remote Computer Wampserver

For error "Connection failed: [hy000] [mysql] [odbc driver 5.1]"

Add a user in phpmyadmin to be accessed by any host.  Grant access accordingly.

Add an exception in Windows Firewall for port 3306 (default MySQL port)

 

Filed under: mySQL, wamp No Comments
14Jun/110

Import Large Files into MySQL phpmyadmin

Start by doing what this site says:

http://daipratt.co.uk/importing-large-files-into-mysql-with-phpmyadmin/

Note:  File extension must be lower case for it to be recognized

The trick to getting it to go much faster is using the CSV using LOAD DATA option on the left side

For the csv file I set the field delimiter(Fields terminated by) to | (vertical bar), no text delimeter(Fields enclosed by),Left Fields escaped by \, and row delimiter(Lines terminated by) to ;  By default it is set to auto, but if it doesn't get the rows right try \n

I used these settings because the data has commas in the contents and CR/LFs also.

It took 30secs to import 60,000 with indexes and 10min to import 2,000,000 with indexes

Filed under: mySQL No Comments