GMap.NET Tutorial – Maps, markers and polygons

GMap.NETGMap.NET LogoThe following is a tutorial for using the excellent GMap.NET control. This text will explain how to place a map control on a form, how to initialize it to show the coordinates you want, how to add markers to it, and how to add polygons.

Download the GMap.NET library here (the so-called “hot build” from 2013 – the latest stable release is from 2011 and had some problems for me).
(Note that I have another tutorial up on how to add routes to GMap.NET).

Setting up

First, create a new C# Windows Forms project in Visual Studio 2010. In your GMap.NET download, you will find DLLs named GMap.NET.Core.dll and GMap.NET.WindowsForms.dll. Place them in a subfolder of your project, and add a reference to both.

Adding GMap.NET references
This will allow your code to access GMap.NET’s classes. Since GMap.NET is a user control, you can add it to your Toolbox. (Of course, you could simply instantiate the GMapControl from your code without ever adding it to your Toolbox, but then you would miss out on setting the control’s properties conveniently through the Properties panel). To add the control to your Toolbox, right-click the Toolbox and select “Choose Items.”

Adding the GMap control to your Visual Studio Toolbox

You’ll find the required assemblies by clicking Browse… and selecting the GMap.NET.WindowsForms DLL. This should contain the GMapControl. Verify that there’s a check next to this control, and when you click OK, the control should be in your Toolbox and can be dragged to a form.

Adding a GMap to a Windows Form

Now add a new form (your fresh C# Windows Application should already have one) and drag the GMapControl to it. Resize it to your liking and call it “gmap” instead of the cumbersome GMapControl1. The control will display as an empty rectangle:

Adding the GMap Control to a form

With the control selected, open the Properties panel. Apart from the usual Control properties, you’ll find some GMap-specific properties there. Now things will get interesting:

Properties for the GMapControl

I’ll explain some of the properties right away:

  • CanDragMap – If true, the user can drag the map using the right mouse button. You’ll probably want to keep this set to true.
  • MarkersEnabled – If true, any markers that you defined will be shown. If not, they won’t appear. Set this to true for now. If you forget, you may pull your hair out figuring out why your markers don’t appear (I did).
  • PolygonsEnabled – Same story here.
  • ShowTileGridLines – If true, GMap.NET will show tile coordinates on the tiles. Not something for a production environment, but it may help with debugging.
  • Zoom, MinZoom, MaxZoom – The Zoom level for Google Maps is somewhere between 0 (zoomed out to global level) to 18 (zoomed in to street level). Zoom is the current zoom level (5 would be good for country level), while MinZoom and MaxZoom should be set to 0 and 18 respectively if you want users to be able to zoom in and out fully. Zooming is done with the mouse wheel.

All these are interesting switches that allow us to define how the map will be shown and interacted with, but they don’t allow us to set where the map data is coming from. As we will see, this must be done in code. Running the program now will result in a persistently blank rectangle where the map is supposed to go.

Initializing the map

Add an onLoad event to your form, and add the following code to it:

private void Form1_Load(object sender, EventArgs e)
  // Initialize map:
  gmap.MapProvider = GMap.NET.MapProviders.BingMapProvider.Instance;
  GMap.NET.GMaps.Instance.Mode = GMap.NET.AccessMode.ServerOnly;
  gmap.SetCurrentPositionByKeywords("Maputo, Mozambique");

Now run the program. A map should appear, centered on the city of Maputo, Mozambique. I’ve set the position using key words recognized by the map data provider, but you can also use latitude/longitude if you want:

gmap.Position = new PointLatLng(-25.971684,32.589759);

While running the program, you will notice that the map can be dragged with the right mouse button, and zooming is done with the mouse wheel. If these operations do not work, then check that you’ve set the GMapControl’s properties correctly in the Properties panel – you may have inadvertently turned off dragging, or fixed the zooming level.

Map Providers

The magic of the GMap.NET library is that is doesn’t merely work with Google Maps. There are other map data providers out there, and GMap.NET supports a slew of them while the gory API details are all neatly hidden away from you. In the example above, I’ve used the BingMapProvider, but other useful ones include:

  • CloudMadeMapProvider
  • GoogleMapProvider – map provider for Google Maps; there are street, satellite and hybrid variants
  • OpenCycleMapProvider
  • OpenStreetMapProvider
  • WikiMapiaMapProvider
  • YahooMapProvider

Interestingly, the Google Map provider is (for me, at least), the slowest one. The applications takes a good number of seconds to start, presumably because a connection with Google is being (slowly) made. The Bing Map provider was much faster. But, of course, the data shown will be different so it all depends on your preferences. At the very least, your map code could have fallback providers.

GMap.NET with Yahoo Provider

Yahoo provider

GMap.NET with Open Street Map provider

Open Street Map provider

GMap.NET with Google Maps provider

Google Maps provider

GMap.NET with Bing Maps provider

Bing Maps provider

The magic of GMap.NET goes further still. When we get to showing markers and polygons on a map, we’ll see that GMap.NET hides provider-specific implementation details away behind a common interface. That means that any marker and polygon code you write for GMap.NET will work with any of the providers. Awesome!

Adding markers

Markers are added in layers that are placed on top of your map, called overlays. You can place any number of markers in an overlay, then add that overlay to the map. The overlay can then be hidden en shown as necessary.

Here is a bit of code that adds an overlay called “markers” to the map, with a single marker in it:

GMapOverlay markersOverlay = new GMapOverlay("markers");
GMarkerGoogle marker = new GMarkerGoogle(new PointLatLng(-25.966688, 32.580528),;

Adding a marker to GMap.NET

A Google-type marker on a Bing Map provider

First, the overlay is created. You can give it a name (optionally), which you can use elsewhere to refer to it (or you could just keep a reference to the overlay instance). Next, an instance of GMarkerGoogle is created. It takes two arguments: a location (a PointLatLng instance) and a marker type.

The marker types are a variety of marker images normally available in the Google Maps API (big balloons in many colors, small balloons in many colors, etc.). Or, you can supply an instance of Bitmap with your own image:

GMarkerGoogle marker = new GMarkerGoogle(new PointLatLng(-25.966688, 32.580528),
  new Bitmap("c:\\images\\mymarker.png");

The marker is added to the overlay, and finally the overlay is added to the map. These markers (and the GMarkerGoogleType instances) work with any map provider!

Of course, you can create additional markers and add them to your overlay. There’s no limit, except in performance. More markers mean that performance goes down.

Adding polygons

Markers may not always cut it. Sometimes you need to delimit an area of your map. For instance, you may need to indicate where new construction will take place, or who owns which land. Polygons allow you to show just that.

The following code shows how to create another overlay, and add a four-point polygon to it.

GMapOverlay polyOverlay = new GMapOverlay("polygons");
List points = new List();
points.Add(new PointLatLng(-25.969562,32.585789));
points.Add(new PointLatLng(-25.966205,32.588171));
points.Add(new PointLatLng(-25.968134,32.591647));
points.Add(new PointLatLng(-25.971684,32.589759));
GMapPolygon polygon = new GMapPolygon(points, "mypolygon");
polygon.Fill = new SolidBrush(Color.FromArgb(50, Color.Red));
polygon.Stroke = new Pen(Color.Red, 1);

First, we create a new overlay. Next, we define a list with all the points in the polygon (this is a four-point polygon, but you can just as easily create many more points). Using these points, we create an instance ofGMapPolygon. For good measure, we tell the polygon how to draw itself using a SolidBrush (semitransparent) for the fill, and a thin red Pen for the stroke. Finally, we add the polygon to the overlay, and the overlay to the map.

Tip: don’t forget to add overlays to the map, or they won’t show up.

Adding a GMap.NET polygon

A polygon showing the area covered by Maputo’s Central Hospital


This tutorial should allow you to get started with GMap.NET. Here are some parting tips:

You can use different map providers. Some are faster than others. Also, some show more data than others. Depending on your location, this can make all the difference (Maputo, Mozambique is such as location – Yahoo Maps has almost no data).

When adding markers or polygons to an overlay, don’t forget to add the overlay to the map.

If your markers don’t show up, verify that you have MarkersEnabled set to True in the Properties panel for your GMapControl instance.

GMap.NET requires an internet connection to function properly. However, if no connection is available, you can still use cached information (if available) to show your maps. To do this,  do:

GMap.NET.GMaps.Instance.Mode = GMap.NET.AccessMode.CacheOnly;

Note: there’s more – see GMap.NET Tutorial – Routes to see how to show routes on your maps.



Funciones matemáticas

Las funciones matemáticas realizan operaciones con expresiones numéricas y retornan un resultado, operan con tipos de datos numéricos.

PostgreSQL tiene algunas funciones para trabajar con números. Aquí presentamos algunas.

abs(x): retorna el valor absoluto del argumento “x”. Ejemplo:

select abs(-20);

retorna 20.

cbrt(x): retorna la raíz cúbica del argumento “x”. Ejemplo:

select cbrt(27);

retorna 3.

ceiling(x): redondea hacia arriba el argumento “x”. Ejemplo:

select ceiling(12.34);

retorna 13.

floor(x): redondea hacia abajo el argumento “x”. Ejemplo:

select floor(12.34);

retorna 12.

power(x,y): retorna el valor de “x” elevado a la “y” potencia. Ejemplo:

select power(2,3);

retorna 8.

round(numero): retorna un número redondeado al valor más próximo. Ejemplo:

select round(10.4);

retorna “10”.

sign(x): si el argumento es un valor positivo devuelve 1;-1 si es negativo y si es 0, 0. Ejemplo:

select sign(-23.4);

retorna “-1”.

sqrt(x): devuelve la raíz cuadrada del valor enviado como argumento. Ejemplo:

select sqrt(9);

retorna “3”.

mod(x,y): devuelve el resto de dividir x con respecto a y. Ejemplo:

select mod(11,2);

retorna “1”.

pi(): devuelve el valor de pi. Ejemplo:

select pi();

retorna “3.14159265358979”.

random(): devuelve un valor aleatorio entre 0 y 1 (sin incluirlos). Ejemplo:

select random();

retorna por ejemplo “0.895562474101578”.

trunc(x): Retorna la parte entera del parámetro. Ejemplo:

select trunc(34.7);

retorna “34”.

trunc(x,decimales): Retorna la parte entera del parámetro y la parte decimal truncando hasta el valor indicado en el segundo parámetro. Ejemplo:

select trunc(34.7777,2);

retorna “34.77”.

sin(x): Retorna el valor del seno en radianes. Ejemplo:

select sin(0);

retorna “0”.

cos(x): Retorna el valor del coseno en radianes. Ejemplo:

select cos(0);

retorna “1”.

tan(x): Retorna el valor de la tangente en radianes. Ejemplo:

select tan(0);

retorna “0”.


Instalando y configurando TS Gateway y TS Web Access

Muchas veces los usuarios tienen la necesidad de poder conectarse remotamente a sus aplicaciones de trabajo diario o a su PC de la oficina de manera segura y sencilla, existen muchas alternativas como pueden ser conectarse a la VPN de su trabajo, instalar uno de los tantos programas que existen en internet de control remoto etc.

Pero una de las formas más practicas seguras y confiables hasta el momento que en particular yo conocía era por Citrix.

Si bien en mi opinión Citrix lleva ventaja sobre la solución que propone Microsoft con Windows Server 2008 Terminal Services RemoteApp, y más en ambientes de grandes empresas, Microsoft 2008 TS es una buena alternativa para poder montarlo en pymes.

A continuación voy a explicar de la mejor manera posible como instalar, configurar y como publicar aplicaciones con terminal services.

Como primera medida tenemos que tener bien en claro como sería un escenario básico de nuestro ambiente de Terminal:

La conexión del punto 1 al 2 se establece por medio del protocolo RDP encapsulado
en un HTTPS (HTTP sobre SSL). Para recibir esta conexión HTTPS en el perímetro
de red, el servidor TS Gateway debe estar ejecutando Internet Information Services (IIS)
Del servidor web. Después de recibir la conexión, el servidor TS Gateway a continuación, elimina los datos HTTPS y envía los paquetes RDP a los servidores de terminal de destino (punto 3) que se encuentran detrás de un segundo firewall interno.

En este escenario Active Directory Domain Services debe estar instalado en el TS Gateway para validar ó denegar las conexiones entrantes.

Para esta ocasión debido a la falta de recursos en mi computadora personal estaremos utilizando para la práctica 1 DC llamado 8KLABDC01 y otro servidor que será el TS Gateway además de alojar las aplicaciones que vamos a publicar 8KLABAP03.

1.       Bueno primero nos logueamos en el servidor 8KLABAP03 con las credenciales administrator de nuestro dominio.
2.       Vamos a la consola de Server manager y expandimos Roles, en el panel de detalle de la derecha seleccionamos la opción Add Roles.
3.       Nos aparecerá una pantalla de información “Before of begin” le damos Next.
4.       En la pantalla Select Server Roles, tildamos la opción de Terminal Services y hacemos click en Next.

En la pantalla Terminal Services nos hace una pequeña introducción a las propiedades de TS leemos y le damos click en Next.
En Roles Services deberemos elegir que rol de Terminal Services va a tener nuestro servidor, en este caso por ahora vamos a marcar Terminal Server y TS Gateway y le damos Next.

Es posible que al marcar TS Gateway nos aparezca un cuadro de dialogo que nos informa que para instalar este rol en el servidor se requieren tener otros roles instalados, le decimos que si que agregue los roles requeridos (Add required Role Services)

8. En la siguiente pantalla solo le damos Next, nos informa que antes de instalar cualquier aplicación a para publicar debemos tener el Terminal Server instalado.

9. En la siguiente pantalla elegimos la opción do not require Network Level Authentication, para que las computadoras que corran con cualquier versión se puedan conectar a este terminal server.

10. En la siguiente pantalla Licensing mode debemos especificar el tipo de licencia de terminal que poseemos, vamos a elegir en este caso configurar más tarde ya que como no poseemos de una licencia, MS nos deja utilizar el servicio por unos 120 días. Le damos Next.

11. En la pantalla Select usersGroups allowed Access to this Terminal Server, deberemos especificar los grupos de usuarios que van a tener acceso al Servidor terminal Server, por defecto ya se encuentra asignado el grupo Administrators, en mi caso cree un grupo adicional que se llama TS Gateway. Le damos Next.

12. Podemos instalar un certificado ya para que la conexión RDP vaya cifrada con la capa SSL y sea más segura, si tenemos ya un certificado podemos aprovechar e instalarlo ahora, si no, después, marcamos la choose a certifícate for SSL encryption later. “Next”, la instalación de un certificado será obligatoria posteriormente.

13. Creamos las directivas de autorización para la conexión usando TS Gateway, elegimos Now. Next.

  1. Seleccionamos los grupos de usuarios que se podrán conectar a travez del TS Gateway. Por default ya aparece el grupo de Administrators. Una vez que seleccionamos a los usuarios que se podrán conectar a travez del TS Gateway le damos Next.

  1. Creamos el servidor TS CAP, que permitirá a los usuarios que agregamos anteriormente a validarse por password o smart card o ambos. Lo dejamos como esta y le damos Next.

  1. En la siguiente pantalla, podemos indicar a que computadoras se podrán conectar los usuarios anteriormente que seleccionamos, para este caso elegimos la opción Allow users to connect to any compute ron the network.

En la siguiente pantalla solo le damos Next

  1. En la pantalla de select Roles services también dejamos todo como esta y le damos Next.

  1. En la siguiente pantalla Web Server (IIS) también le damos Next.
  2. En la pantalla de Roles services de IIS también dejamos todo como esta y seleccionamos Next.

  1. En la pantalla de confirmación, nos muestra un resumen de lo que instalara, con algunas advertencias que arreglaremos más adelante. Hacemos Click en el Boton Install.
  2. Una vez finalizada la instalación de todos los componentes nos muestra un resumen y nos advierte que debemos reiniciar el servidor. Hacemos click en close y luego reiniciamos el equipo.

  1. Una vez reiniciado el equipo nos vamos a Start, Administrative Tools, Terminal Services, TS Gateway Manager para terminar de configurar nuestro TS Gateway.
  2. En la consola de TS Gateway Manager, nos paramos sobre nuestro servidor 8KLABAP03, en el panel de la derecha nos informarma un status del servidor, nos aparecerá un error de que no se instalo aun el certificado de seguridad, el cual vamos a instalar ahora.

  1. Hacemos click derecho sobre el servidor 8KLABAP03, propiedades. Nos abrirá la siguiente pantalla, en la solapa General, podemos limitar la cantidad de conexiones simultaneas al TS Gateway, en este caso lo dejamos como viene por default y que permita el máximo de conexiones simultaneas.

  1. En la solapa SSL Certificate, vamos a crear el certificado de seguridad, aquí vamos a hacer click en create a self-signed certifícate for SSL-encryption y luego Create Certificate debido a que aun no tenemos uno.

  1. En certifícate name escribimos el nombre del servidor completo, y en certifícate location le indicamos la ruta donde se va a guardar el certificado para distribuirlo a los usuarios mas adelante, en mi caso como es una prueba lo dejo en Documents.

  1. Le damos ok.

  1. Vamos a la solapa Server Farm, y agregamos al servidor TS Gateway como miembro de la granja. Escribimos el nombre del servidor y luego click en add. Para terminar click en apply y si queremo le damos un refresh status para verificar el status ok del servidor como miembro de la granja.

Bueno con esto ya tendríamos nuestro servidor TS Gateway funcionando solo resta distribuir el certificado a los clientes y realizar una prueba de conexión.


Postgres ODBC, Linked server, SQl server Reporting Services connection

Connec to Postgres using ODBC connection follow these steps
1) Download Postgres ODBC driver from
or a 64-bit postgres ODBC driver from
2) Install above ODBC drivers
3) Create system DSN … On windows XP > Control Panel > Administrative Tools > Data Sources (ODBC)

4) Go to System DSN Tab > Add > Select PostgresSQL Unicode sriver > Finish

5) On Prompted page add

Data Source — Whatever you want to name the DSN

Database – Name of postgres database

Server – Name of Postgres server

Port:5432 (or whaever you database port is..)
Username and Password: Enter postgres database username /password you wish to use
Test the System DSN by clicking on Test
6) Create Linked Server on SQL Server 2008/2005 to connect to postgres database
Start SQL server management studio
Connect to SQL server instance where who wish to create linked server
Go to ServerObject > linked Servers > New Linked Server
Fill the Linked server properties
Select Provider: Microsoft OLEDB Provider for ODBC Drivers
Product name: postgres (you can put whatever u like)
Data Source: Name of System DSN created in last step
Right click Linked Server and Test Connection
Now you query postgres from SSMS by something like
select * from linkedserver_name.database_name.schema_name.Table
7) Creating Connection to Postgres from SQL Server Reporting Services (SSRS)
Create a New data Source
Name : Test
Data Source Type: ODBC
Connection String: Driver={PostgreSQL UNICODE};Server=testserver;Port=5432;Database=Test;Schema=public;
Connect using: Credentials stored securily in report server, eneter user_name, password and Test Connection
If it is 64-bit driver then
Connection string is something like
Driver={PostgreSQL 64-Bit ODBC Drivers};Server=testserver;Port=5432;Database=test;Schema=public;
Using above techniques you can also connect from SSIS (SQL server Integration Services)


Set up PostgreSQL as a linked server in Microsoft SQL Server 32-bit on a Windows Server 2003 64-bit


  • Machine 1
    OS: Windows Vista Business SP1
    DB: PostgreSQL 8.3, port: 5432
  • Machine 2
    OS: Windows Server 2003 Enterprise x64 Edition SP2
    DB: Microsoft SQL Server X86 Standard 9.00.1399.06, port: 4900


  • Machine 1
    1. Make sure port 5432 is opened in the firewall.
    2. Make sure Machine 2 is granted access to PostgreSQL. The access config file is at C:\Program Files\PostgreSQL\8.3\data\pg_hba.conf in this case. host all all md5 is added to the end of the file.
  • Machine 2
    1. Install 64-Bit OLEDB Provider for ODBC (MSDASQL), the installer file is WindowsServer2003.WindowsXP-KB948459-v2-x64-ENU.exe in this case.
    2. Install 32-bit PostgreSQL ODBC driver, choose the appropriate PostgreSQL version matching the setup (in this case
    3. In SQL Server Management Studio, create a linked server using the following command:

      EXEC sp_addlinkedserver
      @server = '',
      @srvproduct = 'PostgreSQL',
      @provider = 'MSDASQL',
      @provstr = 'Driver=PostgreSQL ANSI;uid=postgres;Server=;Port=5432;database=my_db_name;pwd=my_password';
      EXEC sp_addlinkedsrvlogin
      @rmtsrvname = '',
      @useself = 'false',
      @rmtuser = 'postgres',
      @rmtpassword = 'my_password';
    4. To test the connection:

      SELECT *
      FROM OPENQUERY([], 'SELECT * FROM information_schema.tables')



Instalación y configuración de la plataforma BI de Pentaho.

Con la ultima versión estable de la plataforma BI descargada de la web de Pentaho (la 3.5.2), y siguiendo las instrucciones de Prashant Raju para esta versión en la plataforma Windows utilizando MySql, realizamos la instalación y configuración de nuestro sistema realizando los siguientes pasos:

1) Requisitos previos: maquina virtual Java y la base de datos MySQL (u otra de las soportadas).

Para poder ejecutar la plataforma de BI de Pentaho es necesario disponer de una máquina virtual Java instalada en el equipo donde vamos a trabajar. Pentaho recomienda la versión 1.5 de Sun JRE. Con versiones anteriores no funciona y la 1.6 no esta oficialmente soportada (es la que tengo instalada yo), aunque si funciona.

Para ver la versión instalada, ejecutaremos el comando: java  -version. En el caso de no disponer de la máquina, podemos descargarla en la web de Sun.

A continuación comprobaremos que la variable de entorno JAVA_HOME apunte al directorio donde tenemos instalado Java. Igualmente, la variable PATHtambién debera apuntar al directorio de ejecutables de la instalación de Java. En mi caso, el valor de las variables será el siguiente:

JAVA_HOME   c:\Program Files\Java\jdk1.6.0_17
PATH        c:\Program Files\Java\jdk1.6.0_17\bin;.....

Para configurar las variables, lo realizaremos desde Propiedades del Sistema, Variables de Entorno.

Con respecto a MySQL, en el caso de que no lo tengamos instalado en nuestra máquina, lo descargaremos de la web y realizaremos la instalación según las instrucciones que nos proporcionan en su portal de documentación.

2) Descomprimir los ficheros de la plataforma.

Seleccionamos una carpeta (por ejemplo c:\pentaho), y en ella vamos a descomprimir el fichero Zip que nos hemos bajado de la web. Tras el proceso, tendremos dos carpetas diferenciadas, llamadas administration-console y biserver-ce. La primera carpeta alberga los ficheros de la plataforma de administración, que utilizamos para configurar y administrar el servidor BI (utiliza Jetty). La segunda, es la plataforma de BI propiamente dicha (la que utilizarán los usuarios), que utiliza tomcat.

En este momento, ya podriamos arrancar la plataforma desde los correspondientes scripts que se encuentran en la carpeta c:\pentaho\biserver-ce (start-pentaho.bat para iniciar el servidor y stop-pentaho.bat para pararlo). Este Script arranca en primer lugar la base de datos HSQLDB de ejemplo (donde residen las datos necesarios para el funcionamiento de la plataforma, junto con datos de pruebas para los ejemplos precargados). A continuación, arranca la plataforma de BI, a través del tomcat. Como no queremos trabajar con esa base de datos, sino con MySQL, vamos a proceder a realizar una serie de ajustes antes de arrancar la plataforma.

3) Creación de catalogos en base de datos necesarios para la plataforma.

La plataforma Pentaho necesita dos bases de datos para su funcionamiento (además de la base de datos de test para poder trabajar con el set de ejemplos). Las bases de datos y su cometido son las siguientes:

  • hibernate: esta base de datos almacena la autentificación de usuarios y los datos de autorizaciones, el contenido BI (solution repository) y los origenes de datos disponibles en la plataforma.
  • quartz: es el repositorio para el scheduler Quartz, que es uno de los componentes que forma la plataforma, que nos permite la planificación de procesos dentro del servidor BI.
  • sampledate: contiene las tablas para ilustrar y hacer posible la ejecución de todos los ejemplos por defecto que proporciona la plataforma, para poder hacernos una idea de sus funcionalidades y sus posibilidades de análisis.

Por defecto, los catálogos de estas bases de datos estarán creados en la base de datos HSQLDB que se puede arrancar en la configuración del servidor por defecto.  Para crearlos en MySQL, como es nuestro caso, ejecutaremos los scripts que se encuentran en la carpeta c:\pentaho\biserver-ce\data o bien descargarlos de la web de Prashant Raju. Decido utilizar estos últimos, pues ademas de crear todos los catalogos de tablas, también incluye la carga de datos de ejemplo (paso 5), que es una opción que no incluye la instalación estandar. El orden de ejecución será el siguiente:

mysql> source 1_create_repository_mysql.sql;
 mysql> source 2_create_quartz_mysql.sql;
 mysql> source 3_create_sample_datasource_mysql.sql;
 mysql> source 4_load_sample_users_mysql.sql;
 mysql> source 5_sample_data_mysql.sql;

La ejecución de los scripts sql la realizaremos desde MySQL Query Browser (la herramienta gráfica para ejecución de sentencias SQL) o bien desde linea de comandos con la utilidad mysql que llevar incluido el servidor MySQL para ejecutar scripts. Podiamos haber utilizado cualquier otro editor sql, comoSQuirreL.

4) Configuracion JDBC, Hibernate and Quartz.

Todas las aplicaciones de Pentaho, incluyendo el Pentaho Server, utilizan la conectividad JDBC (Java Database Connectivity) para la comunicación con las bases de datos. Por tanto, será necesario disponer de los correspondientes conectores según la base de datos que vayamos a utilizar. En nuestro caso, vamos a dejar tanto el conector para MySQL (donde iran las bases de datos de Hibernate y Quartz), como el de Oracle (donde va la base de datos del DW). Las carpetas donde vamos a copiar serán las siguientes:

  • C:\Pentaho\biserver-ce\tomcat\common\lib: ubicación de los drivers JDBC para poder utilizar en el servidor Pentaho la base de datos para la que el conector proporciona conectividad.
  • C:\Pentaho\administration-console\jdbc: es necesario ponerlos aquí también para poder definir correctamente las conexiones a base de datos en la consola de administración.

A continuación, configuraremos los ficheros de parametrización del sistema para que Hibernate y Quartz lean de los catalogos de base de datos en Mysql que hemos creado en el punto 3, en lugar de la base de datos HSQLDB proporcionada por defecto.

  • Configuracion de Hibernate (I): en el fichero applicationContext-spring-security-jdbc.xml (ubicado en la carpeta C:\Pentaho\biserver-ce\pentaho-solutions\system), modificaremos la parte que veis subrayada a continuación, con los valores referidos para utilizar MySQL.
<!--  This is only for Hypersonic. Please  update this section for any other database you are using --> 
<bean id="dataSource"
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property  name="url"
value="jdbc:mysql://localhost:3306/hibernate"  />
<property  name="username" value="hibuser" />
<property name="password" value="password" />
  • Configuracion de Hibernate (II): en el fichero applicationContext-spring-security-hibernate.xml (ubicado en la carpeta C:\Pentaho\biserver-ce\pentaho-solutions\system), modificaremos la parte que veis subrayada a continuación, con los valores referidos para utilizar MySQL.
  • Configuración de Hibernate (y III): en el fichero hibernate-settings.xml ( ubicado en la carpeta C:\Pentaho\biserver-ce\pentaho-solutions\system\hibernate), modificaremos la parte que veis subrayada a continuación.

Con la configuración anterior, hemos configurado la seguridad JDBC de la plataforma. Ahora nos falta indicar en los contextos del servidor de aplicación, la ubicación de las bases de datos, para decirle al servidor que lea de las bases de datos en Mysql, utilizando los drivers y la configuración de seguridad realizada anteriormente. Para ello, modificamos el fichero contexts.xml (ubicado en C:\Pentaho\biserver-ce\tomcat\webapps\pentaho\META-INF) de la siguiente manera:

<?xml version="1.0" encoding="UTF-8"?>
 <Context path="/pentaho" docbase="webapps/pentaho/">
 <Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
 factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
 maxWait="10000" username="hibuser" password="password"
 driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate"
 validationQuery="select 1" />
<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
 factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
 maxWait="10000" username="pentaho_user" password="password"
 driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/quartz"
 validationQuery="select 1"/>

Con esta configuración ya tendriamos lista la parte de conectividad con la base de datos. Solo en el caso de que no hubieramos utilizado los scripts de Prashant Raju, tendriamos que realizar un último paso, que sería ejecutar la siguiente sentencia SQL para indicarle al servidor que los datos de ejemplo los hemos cambiado de lugar:

 SET DRIVERCLASS = 'com.mysql.jdbc.Driver’,
 URL = 'jdbc:mysql://localhost:3306/sampledata’,
 WHERE NAME = 'SampleData’

5) Configuración Servidor Apache-Tomcat.

La plataforma Pentaho utiliza Apache-Tomcat como servidor de aplicaciones para desplegar los servicios que la componen. El servidor lleva una configuración por defecto que podemos modificar (por ejemplo, para variar el puerto donde nos conectamos, para el caso de que haya conflicto con otras aplicaciones instaladas en el servidor), la ubicación html, el lenguaje, etc. Para ello, modificaremos el fichero web.xml que se encuentra en la carpeta C:\Pentaho\biserver-ce\tomcat\webapps\pentaho\WEB-INF. Veamos alguna de la cosas que podemos cambiar.


Con este parámetro, le indicamos a la plataforma BI la ubicación de la carpeta pentaho-solutions. Por defecto, tiene el valor c:\biserver-ce\.

En nuestro caso, vamos a cambiar el valor para que apunte a la carpeta donde hemos instalado:



Al instalar, la ruta URL por defecto para acceder a la plataforma será la siguiente: http://localhost:8080/pentaho

Podemos cambiarla si lo desamos modificando el parmetro base_url dentro del mismo fichero. En nuestro caso, como vamos a cambiar el puerto por defecto, modificamos su valor indicando lo siguiente:


Esto nos obligará a cambiar tambien la configuración del fichero server.xml, que veremos mas adelante.


En la ruta C:\Pentaho\biserver-ce\tomcat\conf, tenemos el fichero server.xml, donde podemos modificar el puerto por defecto de nuestro servidor BI (que es el 8080).

<!-- Define a non-SSL HTTP/1.1 Connector on port 8080 -->
 <Connector port="9999" maxHttpHeaderSize="8192"
 maxThreads="150" minSpareThreads="25" maxSpareThreads="75"
 enableLookups="false" redirectPort="8443" acceptCount="100"
 connectionTimeout="20000" disableUploadTimeout="true" />

Ahora pararemos el servidor tomcat y al arrancar la nueva URL de acceso será la siguiente: http://localhost:9999/pentaho

6) Otros elementos. Scripts arranque. Configuración de la publicación de contenidos y del correo SMTP.

Antes de continuar, vamos a ajustar el script de arranque de la plataforma BI, omitiendo la parte de arranque de la base de datos HSQLDB, que por defecto se arranca cuando lanzamos el script start-pentaho.bat (de la carpeta c:\pentaho\biserver-ce). Es tan sencillo como comentar la linea donde se arranca la base de datos. El script quedaría como sigue (la linea subrayada se ha comentado para que no se ejecute):

@echo off
cscript promptuser.js //nologo //e:jscript
rem errorlevel 0 means user chose "no"
if %errorlevel%==0 goto quit
echo WScript.Quit(1); > promptuser.js

if exist "%~dp0jre" call "%~dp0set-pentaho-java.bat" "%~dp0jre"
if not exist "%~dp0jre" call "%~dp0set-pentaho-java.bat"

cd data
rem start start_hypersonic.bat
cd ..\tomcat\bin
set CATALINA_HOME=%~dp0tomcat
set CATALINA_OPTS=-Xms256m -Xmx768m -XX:MaxPermSize=256m -Dsun.rmi.dgc.client.gcInterval=3600000 -Dsun.rmi.dgc.server.gcInterval=3600000
call startup

Ademas de toda la configuración realizada hasta ahora, nos quedan por configurar dos aspectos importantes para el funcionamiento del sistema:

  • Publicación de contenido: por defecto, la publicación de contenido en el servidor BI esta desactivada, por lo que para publicar los informes o análisis que vayamos realizando, lo deberiamos de hacer dejando los ficheros en la correspondientes carpetas del servidor. Pero es mas fácil hacerlo mediante lo que se llama publicación (que veremos en detalle mas adelante). Para habilitar la publicación, modificaremos el ficheropublisher_config.xml, que se encuentra en la carpeta C:\Pentaho\biserver-ce\pentaho-solutions\system. Ahí indicaremos la contraseña de publicación. Por defecto, no tiene ninguna contraseña, y por tanto, no esta habilitada la publicación.
  • Servicio de correo SMTP: como ultimo paso, configuraremos la posibilidad de envio de correo electrónico, a través de un servidor externo (ya que la plataforma no dispone de un servidor de correo electrónico propio). Para ello, configuraremos el fichero email-config.xml en el directorio C:\Pentaho\biserver-ce\pentaho-solutions\system\smtp-email, de la siguiente manera:

En este caso, estoy utilizando gmail para enviar los correos desde la plataforma. Los valores subrayados son los que yo he indicado. En el caso de estar utilizando otro servidor de correo, tendreís que modificar la configuración de servidor, puertos, tipo de conexión, ect, para que funcione según la configuración de este. Con esta funcionalidad habilitamos la distribución de contenido a través del correo electrónico (por ejemplo, para el envío de la ejecución de informes o análisis).

En este momento, ya podemos arrancar la plataforma. Al iniciarla, y conectarnos en el puerto http://localhost:9999, nos aparece la consola de usuario, con una configuración por defecto. Tendría el siguiente aspecto.

Consola de Usuario por defecto

Ya podemos conectarnos con alguno de los usuarios existentes y trastear con el proyecto de ejemplo Steel Wheels o la colección de muestras y ejemplos que incluye la plataforma. Con ellos nos podemos hacer una idea de las posibilidades de análisis de las que vamos a disponer.

Personalizando la plataforma de usuario.

Como queremos personalizar el portal, vamos a realizar algunos cambios en la consola de usuario (también llamada Mantle). Para ello, vamos a utilizar el blog de Prashant Raju donde nos explica muy bien los pasos a seguir para configurar nuestra plataforma. Esta personalización va a consistir en lo siguiente:

No queremos que aparezcan los usuarios de ejemplo al conectarnos al sistema.

Con la configuración por defecto del sistema, cuando entramos al portal de usuario, nos aparece la siguiente ventana:

Aparecen los usuarios de ejemplo, y al seleccionarlos podemos entrar directamente en la plataforma (sin necesidad de recordad su nombre de usuario o contraseña), ya que el sistema nos lo recuerda. Esto no es operativo para un sistema productivo, y por tanto, vamos a modificarlo. Para ello, modificaremos el fichero (ubicando en la carpeta C:\Pentaho\biserver-ce\tomcat\webapps\pentaho\mantleLogin). La configuración por defecto del fichero es la siguiente:

# this file contains settings to configure the login dialog
# flag to turn on/off show users list (overrides pentaho.xml)
# launch PUC in new window (default setting)
# sample users (be sure that each group has the same # of items as the rest)
userIds=joe, suzy, pat, tiffany
userDisplayNames=Joe (admin), Suzy, Pat, Tiffany
userPasswords=password, password, password, password

Vamos a modificar los valores de la siguiente manera:

# this file contains settings to configure the login dialog
# flag to  turn on/off show users list (overrides pentaho.xml)

Reiniciamos el servidor y al entrar en el portal, el aspecto de login habrá variado, apareciendo la siguiente pantalla:

Este login es mas acorde con un sistema donde hay que mantener la seguridad.

Ventana de conexión personalizada para nuestra empresa.

Para modificar el aspecto de la ventana de login, hemos de modificar el fichero PUC_login.jsp que se encuentra en la carpeta C:\Pentaho\biserver-ce\tomcat\webapps\pentaho\jsp. En este fichero hemos modificado textos, alguna de las imagenes que aparecen, hasta conseguir el siguiente aspecto:

Esto es solo un ejemplo sencillo de como podemos ajustar el diseño de la página a las necesidades corporativas de una empresa (logos, infografia, etc). Os dejo el link al fichero PUC_login.jsp modificado.

Configuración de mensajes de login y de mensajes de error.

Para modificar los mensajes de usuario en el momento del login, habrá que modificar el fichero MantleLoginMessages_es.PROPERTIES (para el caso del idioma castellano, o el fichero MantleLoginMessages_en.PROPERTIES en el caso de estar trabajando con el ingles). El fichero se encuentra en dos ubicaciones distintas y habra que modificarlo en ambos casos para que siempre salgan los mismos mensajes. Las ubicaciones son las siguientes:

  • C:\Pentaho\biserver-ce\tomcat\webapps\pentaho\mantleLogin\messages
  • C:\Pentaho\biserver-ce\tomcat\webapps\pentaho\mantle\messages

Cambiaremos los textos de los mensajes, y al grabar el fichero automaticamente seran utilizados por el servidor con los nuevos valores.

Personalización del panel de control y del area de trabajo.

Se pueden personalizar muchisimos aspectos de la consola de usuario (area de trabajo), tal y como nos cuenta Prashant Raju en su blog, desde los logotipos, barras de menu, barra de herramientas, colores, etc. En nuestro ejemplo, vamos a modificar el fichero launch.jsp (ubicado en C:\Pentaho\biserver-ce\tomcat\webapps\pentaho\mantle\launch). En el ejemplo, he modificado el fichero para que en la parte de la derecha aparezca mi blog a los usuarios de la plataforma, en el momento de conectarse. El resultado es el siguiente:

Workspace personalizado

Este es solo un ejemplo sencillo de lo que se puede personalizar, que puede ser casi todo (hasta el código fuente si fuese necesario).

Con todos los elementos que hemos configurado, la plataforma de BI de Pentaho esta preparada y lista para ser utilizada, y ademas personalizada a nuestro gusto o necesidades. A continuación vamos a ir viendo las diferentes herramientas que nos proporciona Pentaho para construir nuestros análisis y la forma de configurar su ejecución dentro de la plataforma BI de Pentaho. Además realizaremos la configuración del metadata y la definición de los cubos Olap que luego nos permitiran realizar los análisis dimensionales.


Conexión (ODBC y JDBC) con M$ SQL Server 2000

Aclaración: este tutorial fue publicado inicialmente en el grupo Pentaho.

Debido a preguntas concurrentes acerca de este tema y el de las conexiones en general, conjuntamente con mi colega Ing. Mariano García Mattío hemos realizado un pequeño manual, en el que explicaremos mediante ejemplos gráficos como establecer desde Pentaho Data Integration 3.2.0 stable conexiones ODBC y JDBC con M$ SQL Server 2000.
SQL Server Enterprise Manager:

Administrador de servicios de SQL Server:

Conexión ODBC
1) Establecer orígen de datos ODBC:
* Ir a Panel de control → Herramientas Administrativas → Orígenes de datos (ODBC)
* Seleccionar la pestaña “DNS de Sistema” y presionar el botón “Agregar…”.
* Seleccionar el controlador “SQL Server” y presionar el botón “Finalizar”.
* Completar la ficha para la creación del origen de datos:
** Nombre: datawarehouseODBC
** Descripción: Conexión ODBC a datawarehouse
** Servidor: TGX
* Una vez completada la ficha presionar el botón “Siguiente >”.
* Completar la información de esta pantalla de acuerdo lo crean conveniente y presionar el botón “Siguiente >”.
* Tildar la opción “Establecer la siguiente base de datos como predeterminada” y seleccionar “datawarehouse”. Presionar el botón “Siguiente >”.
* Completar la información de esta pantalla de acuerdo lo crean conveniente y presionar el botón “Finalizar”.
Finalmente deberíamos tener algo como esto:

2) Establecer conexión ODBC en Pentaho Data Integration 3.2.0 stable:
* En la “Transformación” o “Trabajo” que estemos utilzando, ir a la pestaña “View”:

* Presionar el botón derecho sobre “Conexiones a base de datos” y seleccionar “Nuevo”.
* A continuación llenaremos solo los campos necesarios para establecer la conexión, es decir, los campos de la pestaña “General”:
** Connection Name: Conexión ODBC a datawarehouse
** Connection Type: MS SQL Server
** Access: ODBC
** ODBC DNS Source Name: datawarehouseODBC

* Es conveniente presionar el botón “Probar” para asegurarnos de la conexión es correcta.
* Finalmente presionar “OK”.
Conexión JDBC
1) Configurar Driver JDBC:
* Copiar el driver “sqljdbc.jar” en “[data-integration_home]\libext\JDBC”
* Reiniciar Pentaho Data Integration si es que ya estaba ejecutándose.
2) Habilitar la autentificación estándar en M$ SQL Server 2000:
Este paso es llevado a cabo para que cuando intentemos establecer la conexión con la base de datos no nos arroje el siguiente error: Error de inicio de sesión del usuario ‘sa’. Motivo: no está asociado a una conexión de SQL Server de confianza.
* Abrir SQL Server Enterprise Manager.
* Botón derecho sobre el servidor, en nuestro caso: (local)(Windows NT).
* Seleccionar la opción “Propiedades”.
* Ir a la pestaña “Seguridad” y seleccionar la opción “SQL Server y Windows”.
* Presionar el botón “Aceptar” y reiniciar el servicio.
3) Establecer conexión JDBC en Pentaho Data Integration 3.2.0 stable:
*En la “Transformación” o “Trabajo” que estemos utilzando, ir a la pestaña “View”:

* Presionar el botón derecho sobre “Conexiones a base de datos” y seleccionar “Nuevo”.
* A continuación llenaremos solo los campos necesarios para establecer la conexión, es decir, los campos de la pestaña “General”:
** Connection Name: Conexión JDBC a datawarehouse
** Connection Type: Generic database
** Access: Natie (JDBC)
** Custom Connection URL: jdbc:sqlserver://localhost:1433;databasename=datawarehouse;responseBuffering=adaptive
** Custom Driver Class Name:
** User Name: sa
** Password: sa

* Es conveniente presionar el botón “Probar” para asegurarnos de la conexión es correcta.
* Finalmente presionar “OK”.
Esperamos les sea útil.


How To Install PgAgent On Windows (PostgreSQL Job Scheduler)

This is a surprise for me that PostgreSQL do not have any build-in job scheduler. However It did pack into pgAdmin source but i wonder why it didnt intsall it by default? Here i provide some steps to show how to install pgAgent(Job Scheduler) on windows as services

1) Go to your PgAdim file path like below, please change to your own pgAdmin file path like “C:\Program Files\PostgreSQL\8.2\pgAdmin III”


2) Get pgAgent.sql and execute the script, it will create all pgAgent core tables


3) After executed pgAgent script, user will notice one job icon appear at pgAdmin.


4) Job scheduler is still not working yet, you have to register it as services in windows. Please issue following command on command prompt

C:\Program Files\PostgreSQL\8.2\bin\pgAgent INSTALL pgAgent -u postgres 
-p secret hostaddr= dbname=newdb user=postgres password=secret

please issue C:\Program Files\PostgreSQL\8.2\bin\pgAgent to show usage of pgAgent, where
-u = username
-p = password
“hostaddr= dbname=newdb user=postgres password=secret” = connect string

P.S please notice INSTALL is all uppercase, do not type lowercase , it will not work.

5) After registered service on windows, just go windows service to start it or use net start command.


6) Done, we can start to schedule our job now.



estuve probando hace un tiempito OpenI, en su versión Plugin de Pentaho. Es muy simple la instalación, solo copiar la carpeta en pentaho-solutions/system y la carpeta dentro de pentaho-solutions. Iniciar el bi-server y listo. Una buena opción para reemplazar el viejo pero genial JPivot. Lo descargan de aquí.
Les adjunto una screenshots.


9.9. Date/Time Functions and Operators

Table 9-26 shows the available functions for date/time value processing, with details appearing in the following subsections. Table 9-25 illustrates the behaviors of the basic arithmetic operators (+, *, etc.). For formatting functions, refer to Section 9.8. You should be familiar with the background information on date/time data types from Section 8.5.

All the functions and operators described below that take time or timestamp inputs actually come in two variants: one that takes time with time zone or timestamp with time zone, and one that takes time without time zone or timestamp without time zone. For brevity, these variants are not shown separately. Also, the + and * operators come in commutative pairs (for example both date + integer and integer + date); we show only one of each such pair.

Table 9-25. Date/Time Operators


date '2001-09-28' + integer '7'
date '2001-10-05'

date '2001-09-28' + interval '1 hour'
timestamp '2001-09-28 01:00:00'

date '2001-09-28' + time '03:00'
timestamp '2001-09-28 03:00:00'

interval '1 day' + interval '1 hour'
interval '1 day 01:00:00'

timestamp '2001-09-28 01:00' + interval '23 hours'
timestamp '2001-09-29 00:00:00'

time '01:00' + interval '3 hours'
time '04:00:00'

- interval '23 hours'
interval '-23:00:00'

date '2001-10-01' - date '2001-09-28'
integer '3'

date '2001-10-01' - integer '7'
date '2001-09-24'

date '2001-09-28' - interval '1 hour'
timestamp '2001-09-27 23:00:00'

time '05:00' - time '03:00'
interval '02:00:00'

time '05:00' - interval '2 hours'
time '03:00:00'

timestamp '2001-09-28 23:00' - interval '23 hours'
timestamp '2001-09-28 00:00:00'

interval '1 day' - interval '1 hour'
interval '1 day -01:00:00'

timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'
interval '1 day 15:00:00'

900 * interval '1 second'
interval '00:15:00'

21 * interval '1 day'
interval '21 days'

double precision '3.5' * interval '1 hour'
interval '03:30:00'

interval '1 hour' / double precision '1.5'
interval '00:40:00'

Table 9-26. Date/Time Functions

Return Type

Subtract arguments, producing a”symbolic” result that uses years and months
age(timestamp '2001-04-10', timestamp '1957-06-13')
43 years 9 mons 27 days

Subtract from current_date
age(timestamp '1957-06-13')
43 years 8 mons 3 days

Today’s date; see Section 9.9.4

time with time zone
Time of day; see Section 9.9.4

timestamp with time zone
Date and time; see Section 9.9.4

double precision
Get subfield (equivalent to extract); seeSection 9.9.1
date_part('hour', timestamp '2001-02-16 20:38:40')

double precision
Get subfield (equivalent to extract); seeSection 9.9.1
date_part('month', interval '2 years 3 months')

Truncate to specified precision; see alsoSection 9.9.2
date_trunc('hour', timestamp '2001-02-16 20:38:40')
2001-02-16 20:00:00

extract(field fromtimestamp)
double precision
Get subfield; see Section 9.9.1
extract(hour from timestamp '2001-02-16 20:38:40')

extract(field frominterval)
double precision
Get subfield; see Section 9.9.1
extract(month from interval '2 years 3 months')

Test for finite time stamp (not equal to infinity)
isfinite(timestamp '2001-02-16 21:28:30')

Test for finite interval
isfinite(interval '4 hours')

Adjust interval so 24-hour time periods are represented as days
justify_hours(interval '24 hours')
1 day

Adjust interval so 30-day time periods are represented as months
justify_days(interval '30 days')
1 month

Time of day; see Section 9.9.4

Date and time; see Section 9.9.4

timestamp with time zone
Current date and time (equivalent tocurrent_timestamp); see Section 9.9.4

Current date and time; see Section 9.9.4

If you are using both justify_hours and justify_days, it is best to use justify_hours first so any additional days will be included in the justify_days calculation.

In addition to these functions, the SQL OVERLAPS operator is supported:

(start1, end1) OVERLAPS (start2, end2)
(start1, length1) OVERLAPS (start2, length2)

This expression yields true when two time periods (defined by their endpoints) overlap, false when they do not overlap. The endpoints can be specified as pairs of dates, times, or time stamps; or as a date, time, or time stamp followed by an interval.

SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Result: true
SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Result: false

When adding an interval value to (or subtracting an interval value from) a timestamp with time zone value, the days component advances (or decrements) the date of the timestamp with time zone by the indicated number of days. Across daylight saving time changes (with the session time zone set to a time zone that recognizes DST), this means interval '1 day' does not necessarily equal interval '24 hours'. For example, with the session time zone set to CST7CDT,timestamp with time zone '2005-04-02 12:00-07' + interval '1 day' will produce timestamp with time zone '2005-04-03 12:00-06', while adding interval '24 hours' to the same initial timestamp with time zone produces timestamp with time zone '2005-04-03 13:00-06', as there is a change in daylight saving time at 2005-04-03 02:00 in time zone CST7CDT.

9.9.1. EXTRACT, date_part

EXTRACT(field FROM source)

The extract function retrieves subfields such as year or hour from date/time values. source must be a value expression of type timestamp, time, or interval. (Expressions of type date will be cast to timestamp and can therefore be used as well.) field is an identifier or string that selects what field to extract from the source value. The extract function returns values of type double precision. The following are valid field names:


The century

Result: 20
Result: 21

The first century starts at 0001-01-01 00:00:00 AD, although they did not know it at the time. This definition applies to all Gregorian calendar countries. There is no century number 0, you go from -1 to 1. If you disagree with this, please write your complaint to: Pope, Cathedral Saint-Peter of Roma, Vatican.

PostgreSQL releases before 8.0 did not follow the conventional numbering of centuries, but just returned the year field divided by 100.


The day (of the month) field (1 – 31)

Result: 16

The year field divided by 10

Result: 200

The day of the week (0 – 6; Sunday is 0) (for timestamp values only)

Result: 5

Note that extract‘s day of the week numbering is different from that of the to_char function.


The day of the year (1 – 365/366) (for timestamp values only)

Result: 47

For date and timestamp values, the number of seconds since 1970-01-01 00:00:00-00 (can be negative); for intervalvalues, the total number of seconds in the interval

Result: 982384720

Result: 442800

Here is how you can convert an epoch value back to a time stamp:

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';

The hour field (0 – 23)

Result: 20

The seconds field, including fractional parts, multiplied by 1 000 000. Note that this includes full seconds.

Result: 28500000

The millennium

Result: 3

Years in the 1900s are in the second millennium. The third millennium starts January 1, 2001.

PostgreSQL releases before 8.0 did not follow the conventional numbering of millennia, but just returned the year field divided by 1000.


The seconds field, including fractional parts, multiplied by 1000. Note that this includes full seconds.

Result: 28500

The minutes field (0 – 59)

Result: 38

For timestamp values, the number of the month within the year (1 – 12) ; for interval values the number of months, modulo 12 (0 – 11)

Result: 2

Result: 3

Result: 1

The quarter of the year (1 – 4) that the day is in (for timestamp values only)

Result: 1

The seconds field, including fractional parts (0 – 59[1])

Result: 40

Result: 28.5

The time zone offset from UTC, measured in seconds. Positive values correspond to time zones east of UTC, negative values to zones west of UTC.


The hour component of the time zone offset


The minute component of the time zone offset


The number of the week of the year that the day is in. By definition (ISO 8601), the first week of a year contains January 4 of that year. (The ISO-8601 week starts on Monday.) In other words, the first Thursday of a year is in week 1 of that year. (for timestamp values only)

Because of this, it is possible for early January dates to be part of the 52nd or 53rd week of the previous year. For example, 2005-01-01 is part of the 53rd week of year 2004, and 2006-01-01 is part of the 52nd week of year 2005.

Result: 7

The year field. Keep in mind there is no 0 AD, so subtracting BC years from AD years should be done with care.

Result: 2001

The extract function is primarily intended for computational processing. For formatting date/time values for display, seeSection 9.8.

The date_part function is modeled on the traditional Ingres equivalent to the SQL-standard function extract:

date_part('field', source)

Note that here the field parameter needs to be a string value, not a name. The valid field names for date_part are the same as for extract.

SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
Result: 16

SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
Result: 4

9.9.2. date_trunc

The function date_trunc is conceptually similar to the trunc function for numbers.

date_trunc('field', source)

source is a value expression of type timestamp or interval. (Values of type date and time are cast automatically, totimestamp or interval respectively.) field selects to which precision to truncate the input value. The return value is of type timestamp or interval with all fields that are less significant than the selected one set to zero (or one, for day and month).

Valid values for field are:















SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00

SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-01-01 00:00:00


The AT TIME ZONE construct allows conversions of time stamps to different time zones. Table 9-27 shows its variants.

Table 9-27. AT TIME ZONE Variants

Return Type

timestamp without time zone AT TIME ZONE zone
timestamp with time zone
Treat given time stamp without time zone as located in the specified time zone

timestamp with time zone AT TIME ZONEzone
timestamp without time zone
Convert given time stamp with time zone to the new time zone

time with time zone AT TIME ZONE zone
time with time zone
Convert given time with time zone to the new time zone

In these expressions, the desired time zone zone can be specified either as a text string (e.g., 'PST') or as an interval (e.g., INTERVAL '-08:00'). In the text case, the available zone names are those shown in either Table B-6 or Table B-4.

Examples (supposing that the local time zone is PST8PDT):

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
Result: 2001-02-16 19:38:40-08

Result: 2001-02-16 18:38:40

The first example takes a time stamp without time zone and interprets it as MST time (UTC-7), which is then converted to PST (UTC-8) for display. The second example takes a time stamp specified in EST (UTC-5) and converts it to local time in MST (UTC-7).

The function timezone(zone, timestamp) is equivalent to the SQL-conforming construct timestamp AT TIME ZONE zone.

9.9.4. Current Date/Time

The following functions are available to obtain the current date and/or time:

CURRENT_TIME (precision)
LOCALTIME (precision)

CURRENT_TIME and CURRENT_TIMESTAMP deliver values with time zone; LOCALTIME and LOCALTIMESTAMP deliver values without time zone.

CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, and LOCALTIMESTAMP can optionally be given a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field. Without a precision parameter, the result is given to the full available precision.

Note: Prior to PostgreSQL 7.2, the precision parameters were unimplemented, and the result was always given in integer seconds.

Some examples:

Result: 14:39:53.662522-05

Result: 2001-12-23

Result: 2001-12-23 14:39:53.662522-05

Result: 2001-12-23 14:39:53.66-05

Result: 2001-12-23 14:39:53.662522

The function now() is the traditional PostgreSQL equivalent to CURRENT_TIMESTAMP.

It is important to know that CURRENT_TIMESTAMP and related functions return the start time of the current transaction; their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the “current” time, so that multiple modifications within the same transaction bear the same time stamp.

Note: Other database systems may advance these values more frequently.

There is also the function timeofday() which returns the wall-clock time and advances during transactions. For historical reasons timeofday() returns a text string rather than a timestamp value:

SELECT timeofday();
Result: Sat Feb 17 19:07:32.000126 2001 EST

All the date/time data types also accept the special literal value now to specify the current date and time. Thus, the following three all return the same result:

SELECT now();
SELECT TIMESTAMP 'now';  -- incorrect for use with DEFAULT

Tip: You do not want to use the third form when specifying a DEFAULT clause while creating a table. The system will convert now to a timestamp as soon as the constant is parsed, so that when the default value is needed, the time of the table creation would be used! The first two forms will not be evaluated until the default value is used, because they are function calls. Thus they will give the desired behavior of defaulting to the time of row insertion.