Converting a datetime value to yyyy-mm-ddThh:mi:ss.mmmZ string format

We want to send some date field data up to our Elasticsearch instance in the format yyyy-mm-ddThh:mi:ss.mmmZ.  The source of the data is a DATETIME data type column in our SQL Server 2008 R2 database.

According to BOL to get the format we need we need to run the following (in this example I’m just using the GETDATE() function rather than selecting an actual value from a table.

SELECT CONVERT(VARCHAR(50), GETDATE(), 127);

When I run this I don’t get the “Z” character at the end.

In BOL there is a comment that says “The optional time zone indicator, Z, is used to make it easier to map XML datetime values that have time zone information to SQL Server datetime values that have no time zone. Z is the indicator for time zone UTC-0. Other time zones are indicated with HH:MM offset in the + or – direction. For example: 2006-12-12T23:45:12-08:00.

The reason the original query doesn’t return the “Z” is there is no offset data in the DATETIME data type.

We need to add the offset to the data by casting it as a DATETIMEOFFSET data type before converting to the string, i.e.

SELECT CONVERT(VARCHAR(50), CAST(GETDATE() AS DATETIMEOFFSET), 127);

NB. As we are using GETDATE() you will see the millisecond included in the result but if your original data is only precise to the nearest second then the millisecond value won’t appear. If you have got millisecond data then you could use the STUFF function after conversion to the string to remove them.

Advertisements

Elasticsearch – Installation

We are using Elasticsearch for our search functionality on the new API framework we are developing to replace our legacy applications and I’ve started reading “ElasticSearch Server” by Rafal Kuc and Marek Rogozinski and published by PACKT.

I’ve played about with Elasticsearch and have installed it locally but only know the basics of posting a document and getting it back. I’ll be going over the contents of the book over the next few weeks so will hopefully get a better understanding of how it all works.

Firstly, though here’s how it is installed…

Please note, I’m using a 64-bit version of Windows 7, and apologies in advance if any of the terminology is not spot on (I’m a database developer after all).

C:\elasticsearch-0.90.3\bin\elasticsearch.bat

  • Next you’ll need to download cURL or some equivalent and you can get this from http://curl.haxx.se/.  This is the command you can use to communicate via the Elasticsearch API which os based on an HTTP protocol and REST.
  • Put the curl.exe somewhere on your file system and add the path to the PATH environment variable.
  • You can then run curl commands from a command prompt. To test this you can run the following and you will get a list of all the curl commands you can run

curl.exe --help

  • You can now send something up to Elasticsearch. A basic example is as follows…

curl.exe -XPUT localhost:9200/test/type/1 -d {"test":"text"}

In this example the -X flag tells curl.exe what HTTP method to use – in this case PUT. The endpoint we are doing the “PUT” to is localhost:9200/test/type/1. This is broken down into the following parts:

  1. localhost – this is the host you are posting to
  2. 9200 – this is the default port
  3. test – this first part is the name of the index
  4. type – this second part is the name of the type
  5. 1 – this final part is the id of the document

The -d flag marks the start of the request document. The document in this example is {“test”:”text”} and this should be in JSON format.
In a Windows command prompt you’ll need to escape the double-quote characters with a leading backslash as shown in the following example.
Elasticsearch - Install - curl

Now if you hit enter you should get an “ok” response from Elasticsearch as shown below.

Elasticsearch - Install - curl response

The response from Elasticsearch also returns the index, type and id of the document and this is unique on the Elasticsearch instance. If you send a completely different JSON document up to Elasticsearch with the same index, type and id it will simply overwrite the old document.

Every time you post up to Elasticsearch the version for the document is incremented. In the example above this is the second time I posted up to localhost:9200/test/type/1 so the _version value is 2.

To confirm our document exists we can now call a GET on the same endpoint as in this example.

Elasticsearch - Install - curl get

Now going back to where we ran elasticsearch.bat… This would have opened a new command prompt that looked something like this.

Elasticsearch - Install - 9200

In this example you can confirm that the port 9200 is being used. You’ll need to look for the [http…] bit. You can also see the name of the instance that is currently being used. This is not configured by default and unless you specify a particular value in the startup files Elasticsearch will use a random name, in this case [Boom Boom].

Finally, as mentioned above you have to escape all the double-quote characters if you’re using a Windows Command Prompt so it would be easier to use a better tool. I’ve been using Cygwin but there are probably others tools that people who know more about API’s could recommend.