QuerySurge and Custom Date/Time/Timestamp Formats in Flat Files
Often, in setting up a Flat File Connection in QuerySurge, custom date/time/timestamp formats come into play regarding data in your file. QuerySurge supports a number of formats by default, but your flat files may require other formats. You can modify your Flat File Connection to add (or subtract) formats so that your files can be processed by QuerySurge.
Formatting Date/Times/Timestamps in Flat Files
The date/time/timestamp formatting notation used in QuerySurge Flat File Connections follows Java's SimpleDateFormat convention. A subset of the symbols appears in the following table (note that symbols are case-sensitive):
|M||Month in year|
|d||Day in month|
|H||Hour in day (0-23)|
|k||Hour in day (1-24)|
|K||Hour in am/pm (0-11)|
|h||Hour in am/pm (1-12)|
|m||Minute in hour|
|s||Second in minute|
Default Date/Time/Timestamp Formats for Flat Files
As we noted above, QuerySurge has a number of default formats. These are available to you when you create your Flat File Connection:
|Default Format||Example Date/Time/Timestamp|
|yyyy-MM-dd HH:mm:ss.SSS||2016-01-12 15:12:34.524|
|yyyy-MM-dd HH:mm:ss||2016-01-12 15:12:34|
Adding Custom Date/Time/Timestamp Formats
Custom formats are added at the Connection level in the Connection Wizard. In order to access the formats, you'll need to start the Connection Wizard in Advanced Mode, by clicking on the checkbox in the lower left, once you have opened the Wizard:
As you step through the Wizard, you'll see some additional items. Nothing should be altered until you get to the Date and Time Options:
In the Date/Time Format edit box, you'll see the default formats that we showed above. Note that they appear as a pipe-separated list. When you add new formats, you just add to this pipe-separated list. (If you want to delete a format, you should delete the accompanying pipe-separator.)
Custom Format Examples
In the listing below, we show some custom format examples to help you craft formats that you may need to handle your files:
|Custom Format||Example Date/Time/Timestamp|
|"yyyy-MM-dd hh:mm:ss a"||"2016-03-19 08:11:53 PM"|
|d MMM yyyy HH:mm:ss||4 Jul 2001 12:08:56|
|yyyy/MM/dd HH:mm:ss z||2001/07/04 12:08:56 PDT|
|MMMMM dd yyyy'@'hh:mm:ss||June 04 2001@12:09:56|
There are some important details about how these formats work that you will need to consider as you set up your file:
Note: If your file uses double-quotes around date/time/timestamps, then you must include the double-quotes in your formatting or your file may not parse correctly. If the file does not use double-quotes, then you should not include them in your formatting. If you have a mixture (double-quoted and unquoted), then adding both a double-quoted and an unquoted format is advisable.
Note: Your file will be evaluated and processed as a date/time/timestamp object. It will appear in QuerySurge using the default object formating. So, for example, the datetime format: 2016-03-19 08:11:53 PM will appear in QuerySurge as 2016-03-19 20:11:53.0. If you want to preserve the formatting in your file, then either type the datetime column as a text type (e.g. varchar) or format it in your SQL.
Note: If your timestamp has a timezone, the timestamp value read will be adjusted to the timezone of the OS that your QuerySurge Agents are on. So, for example, if your QuerySurge Agent is on US EDT, and a file contains a timestamp of 2001/07/04 12:08:56 PDT, it will be stored as 2001-07-04 15:08:56.0 - the time is adjusted to EDT. If you are not interested in this adjustment, then you can either: a) set the timezone of the OS that your Agents are running on to the timezone of the file data, or b) type the datetime column as a text type.
A Final Thought
It may take some experimenting to get the formatting that you need, especially if you have a complex format. The formatting syntax is quite flexible, so you should be able to get the formatting code that you need, even if it takes some effort.
Note: If you are interested in formatting numbers rather than dates in Flat Files, see this Knowledge Base article.