CODEWITHSUNDEEP
.netiisloggingiis-7logparser
user3754676
user3754676

Reputation: 311

Log Parser subtracting 5 from UTC

How can i convert the time into UTC-5 . Currently my time is in UTC but i want to subtract 5 hours from that time. 

LogParser.exe "SELECT QUANTIZE(TO_LOCALTIME(TO_TIMESTAMP(date, time)), 3600) AS Hour,  COUNT(*) AS Hits  FROM D:\projects\jetstar\deployed\u_ex150628.log WHERE date>'2015-06-27' and date<'2015-06-29' and cs-uri-query LIKE '%%orig%%' Group By Hour" -o:datagrid

Upvotes: 1

Views: 1473

Answers (2)

Vince De Giorgio
Vince De Giorgio

Reputation: 126

The answer above didn't work for me. QUANTIZE got rid of the minutes. Here's what I used

SUB(TO_TIMESTAMP(date, time), TIMESTAMP('05:00:00', 'hh:mm:ss'))

If you want it as a string

To_String(SUB(TO_TIMESTAMP(date, time), TIMESTAMP('05:00:00', 'hh:mm:ss')), 'dd/MM/yyyy hh:mm:ss')

Upvotes: 1

user3754676
user3754676

Reputation: 311

I got the answer.

IT is using the Sub function.

LogParser.exe "SELECT  SUB( QUANTIZE(TO_TIMESTAMP(date, time), 3600),TIMESTAMP('05:00:00', 'hh:mm:ss'))  AS HOUR,  COUNT(*) AS Hits  FROM D:\projects\jetstar\deployed\u_ex150628.log WHERE date>'2015-06-27' and date<'2015-06-29' and cs-uri-query LIKE '%%orig%%' Group By Hour" -o:datagrid

Upvotes: 1

Related Questions