Category Archives: Database

SQLite – Reduce db file size after removed the records

After i have removed some log entries in a SQLite DB, I found that the database file size stay the same. This is because SQLite will keep the used space and reuse them later.

We can use the VACUUM command to release the empty space.

sqlite3 <your_sqlite_db_file> "VACUUM;"

 

Done =)

Reference:

Advertisements

MSSQL – Select rows which are created within a specific period

Assume the table has a column called CREATED_DATE which stores the creation date. The following SQL make use of the DATEADD() function to select records which are created within the recent 10 days.

SELECT
  *
FROM
  <YOUR TABLE>
WHERE
  CREATED_DATE > DATEADD(day, -10, getdate())

 

We can use different unit in the datepart (The first argument in DATEADD()) as stated in the 2nd reference link below.

Done =)

Reference:

OS X – Fail to start MySQL server which is installed by Homebrew

After i reboot my OS X, i find that i couldn’t start the MySQL server and it throws the following error:

hatuey:5.6.19 yingkityuen$ mysql.server start
Starting MySQL
. ERROR! The server quit without updating PID file (/usr/local/var/mysql/hatuey.local.pid).

 

Go to the above folder and delete the mysql error log which is ended with .err. In my case, i executed the following command:

hatuey:5.6.19 yingkityuen$ rm /usr/local/var/mysql/hatuey.local.err

Where hatuey is my Mac machine name.

This should fix the problem.

Done =)

Reference: Mahdi Yusuf – MySQL ‘The server quit without updating PID file’

PostgreSQL – Set and Get Timezone

There is a timezone configuration on PostgreSQL server. If you want to change the default globally, you can edit the postgresql.conf. On Ubuntu, it is located at

  • /etc/postgresql/<version>/main/postgresql.conf
# - Locale and Formatting -

datestyle = 'iso, mdy'
#intervalstyle = 'postgres'
#timezone = 'Hongkong'
timezone = 'UTC'

 

We could also tweak the timezone only in your current PostgreSQL session.

1. Get the current timezone.

SELECT current_setting('TIMEZONE');

Continue reading PostgreSQL – Set and Get Timezone