Hi guys,
Any one has experienced this in ingesting IBM IPS Database?
I tried to write a SQL query to return some events from the SensorDataAVP1 table. But the time format is actually a string value like this: 2015-05-09T16:08:47+1200
.
I did some Googling, it is ISO8601 format with time zone Z (aka 127). But whatever I try, I always get this error "Conversion failed when converting date and/or time from character string".
The original string is in nvarchar format. So I converted it to varchar format first before converting it to datetime.
And seems the SQL datetime format doesn't support timezone, so I use datetimeoffset instead.
Here is my convert string:
convert(datetimeoffset, convert(varchar(40),time),127) AS time
Any thoughts?
Thanks
Hi guys,
I've gave up converting time by using SQL query. Cause I am not a sql master, so it would be a waste of time to research a solution for that.
So firstly in the sql query, I renamed the time column as fieldname 'time'.
Then ingested the time in a string format directly to Splunk. Then in the props.conf, I used a little of regex to solve the problem.
Here is the sample of my props.conf stanza:
TIME_PREFIX = time="
TIME_FORMAT = %Y-%m-%dT%H:%M:%S
Hi guys,
I've gave up converting time by using SQL query. Cause I am not a sql master, so it would be a waste of time to research a solution for that.
So firstly in the sql query, I renamed the time column as fieldname 'time'.
Then ingested the time in a string format directly to Splunk. Then in the props.conf, I used a little of regex to solve the problem.
Here is the sample of my props.conf stanza:
TIME_PREFIX = time="
TIME_FORMAT = %Y-%m-%dT%H:%M:%S
And I've tried replicating the conversion on my SSMS, which works perfectly:
declare @c nvarchar(50) = '2015-02-28T01:38:28+13:00'
select convert(datetimeoffset,convert(varchar(50),@c,127),127)
But wait, your example here isn't the same example as what you gave.
This example appears compliant (assuming SQL 2012ish, which may or may not be right) with it's convert requirements, specifically that it has "+13:00" whereas your original example doesn't have the colon and is "+1200".
This is probably fixable, but first could you confirm one wasn't a typo?
(Fixable - I'm not sure the best method. You could potentially manually substr all the bits and pieces into a new datetime, or it may be easier to substring out the first 90%, concatenate a colon into it and tack on the final two digits as string manipulation and THEN do your convert).
Hi Rich,
Thanks for your reminder. I did make a mistake there. The timezone part of the original string is something like '+1300' not '+13:00'
Do you think I can convert the original string to a format like '2015-02-28T01:38:28+13:00', then use the same SQL query to fetch events?
Thanks a lot for your reply. Much appreciated!