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 notes about how these formats work that should be considered.
- Note that some of the custom formats above are enclosed in double-quotes, and some formats are not double-quoted. Double-quoted examples illustrate how to enter a format if the date/time/timestamp field in your Flat File is enclosed in double-quotes.
If the 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.
- Your file will be evaluated and processed using QuerySurge's default formatting. So, for example, the datetime 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 typing the datetime column as a text type is the best option.
- If your timestamp has a timezone, the timestamp value read will be adjusted to your current timezone. So, for example, if QuerySurge 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 typing the datetime column as a text type is the best option.
A Final Note
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.