Previous Next

• 04-01-2008, 11:45 AM

  Deicist Joined on 04-01-2008, 3:39 PM Posts 2 Deicist

  Timestamp comparison woes. Reply Contact So, I'm trying to build a console application to put my IIS log files into a MySQL database for later browsing via an ASP.NET page.  I have everything working fine when I try to do a select all type statement, but when I try to do select all records from after a certain date logparser seems to ignore the 'where' clause and gives me all records.  Here's the code I'm using (it's in c#) String query = "SELECT TO_TIMESTAMP(date,time) AS thedatetime, c-ip, s-ip, cs-method, cs-uri-stem FROM " + thisfolder + "ex*.log WHERE thedatetime > TO_TIMESTAMP('" + maxdate + "','yyyy-MM-dd hh:mm:ss')"; 'thisfolder' is a valid folder  maxdate is a date / time in the correct format.  This query should (as far as I can tell) only give me records where the date / time is later than the date / time specified in maxdate.  It doesn't, it gives me all records.   Help please!

  Tags: timestamp

• 04-02-2008, 2:03 AM

  In reply to

  qbernard Joined on 03-25-2003, 10:12 PM Malaysia Posts 2,218 qbernard	Re: Timestamp comparison woes. Reply Contact Before comparison, is the thedatetime in correct format? local time or w3c GMT time? For day query - try set it to (TO_TIMESTAMP(date, time), 'MM-dd'), with this you skip the year/hour/min/sec format. Cheers, Bernard Cheah

• 04-02-2008, 4:40 AM

  In reply to

  Deicist Joined on 04-01-2008, 3:39 PM Posts 2 Deicist

  Re: Timestamp comparison woes. Reply Contact  Thanks for your response... The local time on my Logs is GMT, so there shouldn't be any timezone related issues... also, I've checked the date / time manually and logparser is definitely pulling through records that should be excluded due to the timestamp.   As for your second  idea, I need the  time as well as the date in my timestamp, I'm not looking for records after a certain date, I'm looking for records after a certain time.  Regardless of that though, comparison between two full timestamps (yyyy-MM-dd hh:mm:ss) should work, shouldn't it?

• 04-03-2008, 1:03 AM

  In reply to

  qbernard Joined on 03-25-2003, 10:12 PM Malaysia Posts 2,218 qbernard	Re: Timestamp comparison woes. Reply Contact Well, ya. it should works, but I was just thinking to simplify the comparison. The comparison is tricky just like comparing IP address. I did a test before.. something like where mydate BETWEE 01-02 and ) 01-15, and manage to only display entries from 2nd of Jan to 15th. so the comparison is working. Cheers, Bernard Cheah