pg_sysdatetime

November 6, 2015 ยท View on GitHub

High precision SYSDATETIME() functions for PostgreSQL on Windows, with MS-SQL-Server naming

Useful for PostgreSQL 9.4 or earlier. Normally PostgreSQL will return at best 1 millisecond time precision on windows. Usually 15ms. This module lets you get greater precision.

Note, however, that until Windows Server 2012 and Windows 8 the timer granularity was limited to 1ms. So you'll get timestamps with more digits, but no greater accuracy. You'll just get a more precise timestamp that stays the same for 1ms, then changes again. To get that extra detail needs use of the GetSystemTimePreciseAsFileTime API where available (Win8 / Win2k12) for sub-microsecond resolution. This extension does not do so.

PostgreSQL 9.5 now natively uses GetSystemTimePreciseAsFileTime where available, and always returns high precision timestamps, so you don't need this extension on 9.5. See See GetSystemTimePreciseAsFileTime support for PostgreSQL 9.5. On 9.5 you can just declare LANGUAGE SQL wrappers for sysdatetime etc if you want the MS-SQL-server interfaces. There's no longer any need for C-level code changes.

This module may be compiled using Visual Studio on Windows. MinGW may also work but is untested. Visual Studio 2012 was used in testing.

Quick install from binaries

If you downloaded a binary release zip of this extension for your PostgreSQL major version and platform (32-bit or 64-bit Windows), simply copy the contents of the "lib" folder to your PostgreSQL install's "lib" folder, and do the same for the "share/extension" folder. Then follow the instructions for "Usage (all platforms) below".

Compilation and installation (Windows)

To install this extension you must first compile it. Binaries are not published on the github page, so you'll probably have to do this.

Compiling it is relatively trivial:

  • Install Visual Studio Express 2012 or a compatible product

  • Open pg_sysdatetime.sln in Visual Studio

  • Change the paths to PostgreSQL, using Visual Studio's properties editor, or a simple text editor. If you only have one installed, just ignore the one you aren't interested in. The defaults will be fine except for the version number if you're using a 32-bit Visual Studio and installed PostgreSQL in the default location. To edit:

    • With the properties editor
      • Open the Property Manager, from View -> Other Windows
      • Edit the pg_sysdatetime properties entry from any of the configuration/platform sections. It doesn't matter which, they're all the same file.
      • Change PGMAJORVERSION to your version, e.g. 9.3.
      • If necessary, also change the values of PGBASEDIR_x86 and PGBASEDIR_x64 to point to the 32-bit and 64-bit PostgreSQL installs you want to build against.
      • Save the changes. When you save the changes they'll be applied to all sections.
    • With a text editor
      • Open pg_sysdatetime.props in a text editor
      • Change <PGMAJORVERSION> to your major version, e.g. 9.3
      • edit the values of the <PGBASEDIR_x64> and <PGBASEDIR_x86> elements to point to your Pg install(s), if needed

  • In the toolbar at the top of the window, choose "Release" from the "Solution Configurations" pulldown, and choose the platform you want to compile for (32-bit or 64-bit).

  • From the Build menu, choose Rebuild Solution

If you get an error about libintl.h beSing missing then you've run into a packaging error in the 64-bit installer for PostgreSQL and you will need to copy libintl.h from here into include\libintl.h in your PostgreSQL install then try the compile again.

After the compile completes, copy pg_sysdatetime--1.0.sql and pg_sysdatetime.control to the share\extension directory of the PostgreSQL install you compiled the extension for.

Copy x64\Release\pg_sysdatetime.dll (for x64 builds) or x86\Release\pg_sysdatetime.dll (for x86 builds) to your PostgreSQL install's lib directory.

Installation (Linux)

Install as you would any other PGXS extension, e.g.

make
sudo make install

or if pg_config isn't on your default PATH, and replacing /usr/pgsql-9.3/bin with the path to pg_config:

PATH=/usr/pgsql-9.3/bin:$PATH make
sudo PATH=/usr/pgsql-9.3/bin:$PATH make install

Usage (all platforms)

Connect to the database you want to install the extension into, as a superuser, and run:

CREATE EXTENSION pg_sysdatetime;

Three functions are provided. All return a timestamp with the highest currently available precision.

  • sysutcdatetime - returns a timestamp without time zone in UTC
  • sysdatetime - returns a timestamp without time zone in local time as defined by TimeZone
  • sysdatetimeoffset - returns a timestamp with time zone for the current UTC timestamp

System timer frequency adjustment

Because GetSystemTimeAsFileTime may return much coarser timestamps depending on hardware and operating system/version, the extension can use Windows Multimedia features to request a higher timer resolution the first time it is run. Just add:

pg_sysdatetime.adjust_timer_resolution = on

to your postgresql.conf.

The setting change can be applied with a pg_ctl reload and can be set per-session by the superuser.

Querying system time resolution

You can use use clockres.exe from SysInternals to check your system's clock resolution (see link below), and can use:

powercfg -energy -duration 5

to produce a report showing which applications have timer resolution requests active.

Future work

Future work could permit the use of GetSystemTimePreciseAsFiletime where running on Windows Server 2012 or Windows 8.

References