Archive for the ‘Database’ Category

Android: Sqlite long to datetime

August 21, 2011

If you store time using android.text.format.Time.toMillis, it will end up in the sqlite as a 13 chars “text/numeric”, something like “1311123600000”. You can do the following to make them human readable:

SELECT datetime(start/1000, 'unixepoch') as start, datetime(end/1000, 'unixepoch') as end from countdowns

//output: 2011-07-20 01:00:00 (I think this means 12:00 AM but I am not sure)

Advertisements

Best database field type for a URL

January 23, 2010

“< MySQL 5.0.3 use TEXT

or >= MySQL 5.0.3 use VARCHAR(2083)


Here

Convert Excel file with unicode characters to MySQL

May 17, 2009

Scenario: It takes you the whole morning to try to get data in an Excel file into MySQL table. You found out there were special characters such as “5┬Ám” inside it.

Solution: Save the file as .txt (Tab delimited or MS-DOS or Machintosh) and make sure it’s encoded in utf-8, etc.

Steps:

  1. Create table in MySQL db using the sample (with only headers trimming off the content) csv file saved from the .xls file;
  2. Save the .xls as Tab delimited .txt;
  3. Load data into MySQL using the following statement (as produced on Windows for tab delimited, double quote inclosing fields.) See here for details:
  4. LOAD DATA LOCAL INFILE '/STUFF/Projects/_tmp/reference.txt' INTO TABLE reference
    FIELDS TERMINATED BY '\t' ENCLOSED BY '"'
    LINES TERMINATED BY '\n';

  5. Clean up the table: You may need to delete the some rows if the Excel table/sheet headers appear as data rows, etc.

Adding index for a table

August 3, 2007

In Excel:

Approach #1: In the cell 1 of the index row, type in “1” and cell 2, type in “2”. Select both cells. Excel highlights both cells with bold-stroked box. There is a small handler on the bottom-right corner (Supposed to be a feature called “Auto Fill”?) Hold and drag the handler all the way down to the last cell on the index row. Excel should autofill all the rest of the cells.

Approach #2: In the cell 1 of the index row, type in “1”. In cell 2, type in “=ROW_NUM1+1” (For example, if this is row A, type in “=A1+1”. What this formula does is to increment the value of cell 1 by 1, which equals to 2.) Select cell 2; drag the small handler on the bottom-right to all the way down the last cell.

In MySQL:

Approach #3: Create a column which is primary key, data type of int and attribute of AUTO_INCREMENT.

convert string into date

June 23, 2007

I wanted to convert a column whose data type of string into that of date.

First add a column with data type of date;

Then:

UPDATE `myTable` SET `REALDATE`= str_to_date( `SRTDATE` , '%m/%d/%Y' );

This will convert ’06/23/2007′ in STRDATE into ‘2007-06-23’ in REALDATE.

How to copy columns from one table into another table

May 8, 2006

Example:

INSERT INTO staff( firstName, lastName, address, homePhone, cellPhone, latitude, longitude )
SELECT firstName, lastName, address, homePhone, cellPhone, latitude, longitude
FROM gfxContact

Data normalization

May 6, 2006

I read about “database normalization” today in the book “PHP Essentials”, which introduces three of the five (?) “normal forms”. I don’t quite get it but I am glad that I came across it:)

I got some similar articles when smart googling “database normalization”. Here are some of them: