"Error parsing column 3 (TimeStamp=1/29/2023 9:10:58 AM -06:00 - Object)\"}" about serilog-ui

[mo-esmp]

I will try to fix it tonight and publish new package

[mo-esmp]

Yes, I did. It's the problem with parsing the date value in DB to DateTime

[mruch2]

PR?

I resolved the issue by changing the datetime column type in sql from datetimeoffset(7) to datetime2(7).

[NaserElziadna]

got same issue .

also getting faild to fetch issue

did you solve it ?

[mruch2]

@NaserElziadna no I have not.

@mo-esmp have you had a chance to look at this?

[NaserElziadna]

@NaserElziadna no I have not.

@mo-esmp have you had a chance to look at this?

still cant figure this out

i will updated here if i do so

[mo-esmp]

Sorry guys for the inconvenience, I'm on a trip and I will check within the next few days. I guess the problem is -06:00 (time zone) at the end of the date column.

[NaserElziadna]

@mo-esmp
its very urgent that i provide a solution to my boss by tomorrow.

Note :: i had a very hard time to convince my manger to accept working with this pakcage

[NaserElziadna]

@mo-esmp thank you very much , waiting for your update

[mruch2]

Thank you @mo-esmp I appreciate your prompt response and resolution. Let me know when you have it updated and pushed and I will test it and provide feedback.

[mruch2]

@mo-esmp trying to speed you up, I think this may help
File: SerilogUIMiddleWare.cs
Line: 157, 158

Adjust to

// String with date and offset dateString = "05/01/2008 +7:00"; if (DateTimeOffset.TryParse(dateString, out parsedDate)) Console.WriteLine("{0} was converted to {1}.", dateString, parsedDate);

Source from:
https://learn.microsoft.com/en-us/dotnet/api/system.datetimeoffset.tryparse?view=net-7.0

[mo-esmp]

@mruch2 and @NaserElziadna which version of Serilog.Sinks.MSSqlServer you are using?

[NaserElziadna]

@mo-esmp i am using Serilog.Sinks.MSSqlServer 6.2.0

[mo-esmp]

I tried to reproduce the error but everything works fine for me. Could you please provide a sample project by sharing a repo or a backup of your sample logs to restore on my DB?

[NaserElziadna]

@mo-esmp badly i cant share a db backup or repo - but can you share the timestamp format that is been saved in your LogTable.

so , i just change mine as yours

[mo-esmp]

First, I'm just wondering how you got the time zone in the timestamp column


Second, the error message @mruch2 shared is about column order and basically, it should not be a problem!
https://github.com/mo-esmp/serilog-ui/blob/4434538f0fb6aca39b810ec4389724daac6e7c92/src/Serilog.Ui.MsSqlServerProvider/SqlServerDataProvider.cs#L47

[NaserElziadna]

@mo-esmp here's what i have

[NaserElziadna]

@mo-esmp

[NaserElziadna]

@mo-esmp , this one of log rows , and i think its about the issue i have

Message
Error parsing column 3 (TimeStamp=2/1/2023 1:25:13 PM +02:00 - Object)

MessageTemplate
Error parsing column 3 (TimeStamp=2/1/2023 1:25:13 PM +02:00 - Object)

Level
Error

TimeStamp
2023-02-01 13:25:44.9103720 +02:00

Exception
System.Data.DataException: Error parsing column 3 (TimeStamp=2/1/2023 1:25:13 PM +02:00 - Object)
---> System.InvalidCastException: Object must implement IConvertible.
at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
at Deserialize37774c75-f66d-4eef-9a57-079c9976f7b4(IDataReader )
--- End of inner exception stack trace ---
at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value) in //Dapper/SqlMapper.cs:line 3634
at Deserialize37774c75-f66d-4eef-9a57-079c9976f7b4(IDataReader )
at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in //Dapper/SqlMapper.Async.cs:line 439
at Serilog.Ui.MsSqlServerProvider.SqlServerDataProvider.GetLogsAsync(Int32 page, Int32 count, String level, String searchCriteria, Nullable1 startDate, Nullable1 endDate)
at Serilog.Ui.MsSqlServerProvider.SqlServerDataProvider.FetchDataAsync(Int32 page, Int32 count, String logLevel, String searchCriteria, Nullable1 startDate, Nullable1 endDate)
at Serilog.Ui.Web.SerilogUiMiddleware.FetchLogsAsync(HttpContext httpContext)
at Serilog.Ui.Web.SerilogUiMiddleware.Invoke(HttpContext httpContext)

properties

Serilog.Ui.Web.SerilogUiMiddleware
0HMO48RN29Q3G:00000005
/logs/api/logs
0HMO48RN29Q3G
Guest

[NaserElziadna]

@mo-esmp any updates ?

[mo-esmp]

Sorry, I'm out now and when I return to the hotel I will check

[NaserElziadna]

sure , thanks

did you get any basic understanding of the issue from what i sent

[NaserElziadna]

@mo-esmp will you be able to fix this issue and upload the fixes to the nuget market by today evening ?
(or should i find an excuse for my boss 😜)

[NaserElziadna]

** @mo-esmp heres a fix , (i think)
File: SerilogUIMiddleWare.cs
Method: FetchLogsAsync**

private async Task FetchLogsAsync(HttpContext httpContext)
{
httpContext.Request.Query.TryGetValue("page", out var pageStr);
httpContext.Request.Query.TryGetValue("count", out var countStr);
httpContext.Request.Query.TryGetValue("level", out var levelStr);
httpContext.Request.Query.TryGetValue("search", out var searchStr);
httpContext.Request.Query.TryGetValue("startDate", out var startDateStar);
httpContext.Request.Query.TryGetValue("endDate", out var endDateStar);

int.TryParse(pageStr, out var currentPage);
int.TryParse(countStr, out var count);

var formats = new string[] {
    "M/d/yyyy h:mm:ss tt zz",
    "M/dd/yyyy hh:mm:ss",
    "MM/dd/yyyy HH:mm:ss",
    "MM/d/yyyy hh:mm:ss tt",
    "M/dd/yyyy hh:mm:ss tt",
    "MM/dd/yyyy hh:mm:ss tt",
    // Add other date and time format strings here
};
var culture = CultureInfo.InvariantCulture;
DateTime startDate, endDate;
if (!DateTime.TryParseExact(startDateStar, formats, culture, DateTimeStyles.None, out startDate))
{
    startDate = default;
}
if (!DateTime.TryParseExact(endDateStar, formats, culture, DateTimeStyles.None, out endDate))
{
    endDate = default;
}

if (endDate != default)
    endDate = new DateTime(endDate.Year, endDate.Month, endDate.Day, 23, 59, 59);

currentPage = currentPage == default ? 1 : currentPage;
count = count == default ? 10 : count;

var provider = httpContext.RequestServices.GetService<IDataProvider>();
var (logs, total) = await provider.FetchDataAsync(currentPage, count, levelStr, searchStr,
    startDate == default ? (DateTime?)null : startDate, endDate == default ? (DateTime?)null : endDate);
//var result = JsonSerializer.Serialize(logs, _jsonSerializerOptions);
var result = JsonConvert.SerializeObject(new { logs, total, count, currentPage }, _jsonSerializerOptions);
return result;

}

Here are some additional format strings that can be used with the DateTime.ToString method in C#:

1. "MM/dd/yyyy" - month/day/year
2. "dd/MM/yyyy" - day/month/year
3. "yyyy/MM/dd" - year/month/day
4. "dd-MM-yyyy" - day-month-year
5. "dd.MM.yyyy" - day.month.year
6. "MMM dd, yyyy" - abbreviated month name day, year
7. "MMMM dd, yyyy" - full month name day, year
8. "dd MMM yyyy" - day abbreviated month name year
9. "dd MMMM yyyy" - day full month name year
10. "yyyy MMMM dd" - year full month name day
11. "yyyy-MM-dd" - year-month-day
12. "MM-dd-yyyy" - month-day-year
13. "dd.MM.yy" - day.month.two digit year
14. "MM/dd/yy" - month/day/two digit year
15. "dd-MMM-yy" - day-abbreviated month name-two digit year
16. "dd MMM yy" - day abbreviated month name two digit year
17. "dddd, dd MMMM yyyy HH:mm:ss" - full day name, day full month name year hour:minute:second
18. "dddd, dd MMMM yyyy h:mm:ss tt" - full day name, day full month name year hour:minute:second AM/PM
19. "dddd, dd MMMM yyyy H:mm:ss" - full day name, day full month name year hour:minute:second (24-hour format)
20. "dddd, dd MMM yyyy HH:mm:ss" - full day name, day abbreviated month name year hour:minute:second
21. "dddd, dd MMM yyyy h:mm:ss tt" - full day name, day abbreviated month name year hour:minute:second AM/PM
22. "dddd, dd MMM yyyy H:mm:ss" - full day name, day abbreviated month name year hour:minute:second (24-hour format)
23. "MM/dd/yyyy hh:mm tt" - month/day/year hour:minute AM/PM
24. "MM/dd/yyyy HH:mm" - month/day/year hour:minute (24-hour format)
25. "MM/dd/yyyy hh:mm:ss tt" - month/day/year hour:minute:second AM/PM
26. "MM/dd/yyyy HH:mm:ss" - month/day/year hour:minute:second (24-hour format)
27. "dd-MMM-yyyy hh:mm tt" - day-abbreviated month name-year hour:minute AM/PM
28. "dd-MMM-yyyy HH:mm" - day-abbreviated month name-year hour:minute (24-hour format)
29. "dd-MMM-yyyy hh:mm:ss tt" - day-abbreviated month name-year hour:minute:second AM/PM
30. "dd-MMM-yyyy HH:mm:ss" - day-abbreviated month name-year hour:minute:second (24-hour format)
31. "dd MMM yyyy hh:mm tt" - day abbreviated month name year hour:minute AM/PM
32. "dd MMM yyyy HH:mm" - day abbreviated month name year hour:minute (24-hour format)
33. "dd MMM yyyy hh:mm:ss tt" - day abbreviated month name year hour:minute:second AM/PM
34. "dd MMM yyyy HH:mm:ss" - day abbreviated month name year hour:minute:second (24-hour format)
35. "dd MMMM yyyy hh:mm tt" - day full month name year hour:minute AM/PM
36. "dd MMMM yyyy HH:mm" - day full month name year hour:minute (24-hour format)
37. "dd MMMM yyyy hh:mm:ss tt" - day full month name year hour:minute:second AM/PM
38. "dd MMMM yyyy HH:mm:ss" - day full month name year hour:minute:second (24-hour format)
39. "yyyy-MM-ddTHH:mm:ss" - year-month-day 'T' hour:minute:second (ISO 8601 format)
40. "yyyy-MM-ddTHH:mm:ss.fff" - year-month-day 'T' hour:minute:second.milliseconds (ISO 8601 format)
41. "yyyy-MM-ddTHH:mm:ss.fffffff" - year-month-day 'T' hour:minute:second.microseconds (ISO 8601 format)
42. "yyyy-MM-ddTHH:mm:ss.fffffffff" - year-month-day 'T' hour:minute:second.nanoseconds (ISO 8601 format)
43. "ddd, dd MMM yyyy HH':'mm':'ss 'GMT'" - abbreviated day name, day abbreviated month name year hour:minute:second GMT
44. "dddd, dd MMMM yyyy HH':'mm':'ss 'GMT'" - full day name, day full month name year hour:minute:second GMT
45. "yyyy-MM-ddTHH:mm:ss.ffffffZ" - year-month-day 'T' hour:minute:second.microseconds time zone (ISO 8601 format)
46. "yyyy-MM-ddTHH:mm:ss.fffffffZ" - year-month-day 'T' hour:minute:second.nanoseconds time zone (ISO 8601 format)
47. "MMMM dd yyyy" - full month name day year
48. "dddd, dd MMMM yyyy" - full day name, day full month name year
49. "dddd dd MMMM yyyy" - full day name day full month name year
50. "dddd, MMMM dd yyyy" - full day name, full month name day year

Note that these format strings can be combined and customized to match your specific needs.

[mruch2]

@mo-esmp to reproduce the error change the SQL column datatype for the TimeStamp to datetimeoffset(7)

Then let it log a few events with the datetime offset information

[NaserElziadna]

@mo-esmp is there any updates ?

[NaserElziadna]

@mo-esmp ?

[mo-esmp]

On the way home, tonight I will fix it, sorry again.

[NaserElziadna]

@mo-esmp hi , do you need any help with solving the issue ?

when will the update be avaible ?

[mo-esmp]

If you could send the PR to fix the problem, I would appreciate that and when I have time I take of care it properly. Last night I arrive at 2 AM and am now at work.

[NaserElziadna]

@mo-esmp sorry man , i am not valid that much time.

i have already provide a code solution on the comments above.

@mruch2 do you have time to do this?