Working with time intervals in PostgreSQL and Excel

By default, PostgreSQL (PSQL to its friends) formats time intervals in a way that makes it much more human-readable, which is nice until you have intervals longer than a day and you want to manipulate the data further in something like Microsoft Excel.

To make the time intervals machine-readable again, you can use something like the following (which I found here):

SELECT date_part('epoch', time_interval_field) * INTERVAL '1 second';

This extracts the number of seconds from your interval, and then multiplies it by an interval in order to convert it back to the INTERVAL type. Seems silly, but this forces the formatting into hh:mm:ss, so if you have more than 24 hours, rather than seeing 1 day 12:00:35.12312, you’ll get 36:00:35.12312.

Then all that remains is to use a custom format in Excel when loading your data:

[h]:mm:ss

The square brackets tell Excel to allow values of more than 24 hours (rather than calculating hours % 24 as it would without), and the rest is fairly self-explanatory. If you care about milliseconds, you can include them with [h]:mm:ss.00000.

Reset sa password in SQL server

By putting SQL Server into single-user mode, you can log in using any Windows administrator account with sysadmin permissions, even if an equivalent SQL login does not exist. This is very useful when you’ve lost sysadmin access (not-entirely-hypothetical example: someone set up a database, removed all sysadmin accounts except sa, and then couldn’t remember the sa password a few weeks down the line), since you can reset the sa password (or the passwords for any other account, for that matter). This post has more details, but essentially:

  1. Open the SQL Server Configuration Manager
  2. Click on the SQL Server 200{5,8} Services leaf, and stop the SQL Server instance you want to put into single-user mode
  3. Open the properties of the instance, go to the Advanced tab, and in the Startup Parameters option, add “;–m” (exactly, without quotes) to the end of the existing value
  4. Click OK, and restart the SQL Server instance
  5. You can now use sqlcmd with a Windows administrator login to execute SQL commands against the instance
  6. Once you’ve finished, don’t forget to remove the -m parameter and restart the SQL instance again to leave single-user mode 😉

SQL RAND() gotcha

I was using RAND() in an INSERT statement, using values SELECTed from a different table. I assumed that RAND() would be executed for each row from the SELECT statement, but instead I discovered it is only executed once per query. Simple gotcha.

INSERT		TableName (ColumnA, ColumnB, ColumnC)
-- RAND() is executed once per query, so all inserted rows will have the same value
SELECT		ValueA, ValueB, RAND() 
FROM		AnotherTable

Depending on what you’re trying to achieve, using RAND(seed) with an integer seed value taken from the values being inserted forces the function to evaluate once per row with the different seed, resulting in different values. However, since RAND is pseudo-random, using the same seed value will result in the same output.