.NET ICompositeUserType vs IQueryable.Where

[Denis Pujdak]

Hi everyone,

I got stuck on my implementation using composite user type Instant:

public class InstantUserType : ICompositeUserType
{
    public override string[] PropertyNames

    {

        get

        {

            return new string[2] { "Utc", "Local" };

        }

    }

    public override IType[] PropertyTypes

    {

        get

        {

            return new IType[2] { NHibernateUtil.UtcDateTime, NHibernateUtil.DateTime };

        }

    }

    public override Type ReturnedClass

    {

        get

        {

            return typeof(Instant);

        }

    }

}

This it strange because when I use comparison like:

var dueDate = new Instant(DateTime.UtcNow);
var result = ((IQueryable)myRepository).Where(x => x.DueDate <= dueDate).ToList();

the generated query looks like:

select * from SomeTable where DueDateUtc <= @p0 and DueDateLocal <= @p0

Does anyone know what can I do to prevent NHibernate from searching by the second parameter. It must be like:

select * from SomeTable where DueDateUtc <= @p0

Thanks.

[Oskar Berggren]

Comparison operators on multi-column user types aren't very useful. The behaviour you see will sometimes be useful for equaliye operators though.

What you are trying to do looks a bit redundant, and I would suggest you avoid storing what is essentially the same value twice, unless you have a very clear idea on what you will gain by it.

You could explore using a component instead. That way you can write your queries with the condition on the proper "subvalue".

/Oskar

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nhusers+unsubscribe@googlegroups.com.
To post to this group, send email to nhu...@googlegroups.com.
Visit this group at https://groups.google.com/group/nhusers.
For more options, visit https://groups.google.com/d/optout.

[Oskar Berggren]

By the way, "Instant" sounds closely related to the existing .Net type DateTimeOffset.

/Oskar

[Michael Powell]

On Tuesday, January 24, 2017 at 8:29:26 AM UTC-5, Denis Pujdak wrote:

Hi everyone,

I got stuck on my implementation using composite user type Instant:

I'm not sure what you are trying to do there, but if a Utc/Local switch is your goal, it's a flawed approach IMO.

You, your customers, and/or end-users are much better served by leaving such decisions to whatever Views are involved. Then just agree that the database will contain Utc, or a known local, i.e. "base unit" if you will.

If you really need to store "local" versus Utc details, then consider a VARCHAR or NVARCHAR capable of handling an ISO format.

[Oskar Berggren]

And don't forget, if you store a local time and are affected by DST changes, make sure you can calculate the exact time around when summer time changes back to standard time since it's easy to get into ambigous values here. This would require you to store the offset (in which case you have a DateTimeOffset), a time zone moniker, or at the very least a single bit flag.

/Oskar

[Denis Pujdak]

Thanks everyone for reply,

The reason why I need Local Time column is to use it on the external reporting tool. The app doesn't use it at all, just saves users time among utc.

[Denis Pujdak]

From http://blog.nodatime.org/2011/08/what-wrong-with-datetime-anyway.html

DateTimeOffset also isn't a good type to use if you want to tie yourself to a specific time zone, because it has no idea of the time zone which gave the relevant offset in the first place. As of .NET 3.5 there's a pretty reasonable TimeZoneInfo class, but no type which talks about "a local time in a particular time zone". So with DateTimeOffset you know what that particular time is in some unspecified time zone, but you don't know what the local time will be a minute later, as the offset for that time zone could change (usually due to daylight saving time changes).

[Denis Pujdak]

Ok. Thanks Oskar. I will try to use a component instead.

[Denis Pujdak]

Edited

On Wednesday, 25 January 2017 05:42:40 UTC-8, Denis Pujdak wrote:

To be understood DateTimeOffset has an offset not timezone (e.g. -08:00)

For different countries the daylight saving time can be different. If local time is 2016-03-15 12:00am then the UTC for different countries can be different (08:00pm or 07:00pm) in DST period. So in this case the UTC I can get from DateTimeOffset could be wrong because I don't know for which country (TimeZone) is it.

E.g.
(UTC-08:00) Pacific Time (US and Canada), DST: Second Sunday March <-> First Sunday November

and

(UTC-08:00) Baja California, DST: First Sunday April <-> Last Sunday October

That's reason why we cannot use DateTimeOffset for instant values.

[Oskar Berggren]

No,  I believe you are mistaken.

There is only one UTC. The UTC is never different. It's value is always the same everywhere.

The DateTimeOffset class is used to represent a specific point in time, and it can also store that instant associated with an offset. The stored offset should always be the stored value's exact difference from UTC.

So, provided that you grab the local time and the local offset currently in effect at that time, when the DTO is constructed, it can always be converted to an unambiguous UTC value without knowing the geopolitical time zone information.

For the opposite transform, that is, converting a DTO to a local time and local offset appropriate to a specific region at that point in time, you would need to specify what time zone information to use, obviously. You don't need to record the time zone name unless you expressly need to display that information later - but I didn't see you do that in your Instant class.

If you ask the computer for the current local DTO, it will return different offset depending on if DST is in effect or not. Regardless, the recorded value can be unambiguously converted to correct UTC.

You can also have a DTO, add an hour to it, and get a new DTO which represents a point in time exactly one hour later - BUT it might not be a correctly expressed local time in your intended time zone, because it won't update the offset. For that you need help from a time zone. If it's the current timezone in effect, it should be easily achieved by just roundtripping the DTO to UTC and back.

What WILL get you into trouble is if you store the local time _without_ offset or timezone moniker (which should both change between DST and regular), because then some values will be ambiguous. You can't tell if it was the last hour of DST or the first hour of standard time.

This is my understanding anyway. Happy to be corrected if I'm wrong.

Getting back to your Instant and it's mapping. If the stored local time is only needed for reporting, I wonder if you have considered using a view to calculate it when needed and not bother the application with it? Or a DB trigger to have the DB do it on UPDATE.

/Oskar

[Denis Pujdak]

Thanks for everyone,

I found a solution. I just added similar properties to the Instant time e.g. Instant.Utc, Instant.Local

So now I can use them in the IQueryable conditions:

var dueDate = new Instant(DateTime.UtcNow);

var result = ((IQueryable)myRepository).Where(x => x.DueDate.Utc <= dueDate.Utc).ToList();

gives

select * from SomeTable where DueDateUtc <= @p0

And that is exactly what I wanted.

SOLVED :-)

[Michael Powell]

On Thu, Jan 26, 2017 at 5:20 AM, Oskar Berggren
<oskar.b...@gmail.com> wrote:
> No, I believe you are mistaken.
>
> There is only one UTC. The UTC is never different. It's value is always the
> same everywhere.

This is exactly correct and is why if you are storing potentially
ambiguous date/time, you are asking for trouble.

> The DateTimeOffset class is used to represent a specific point in time, and
> it can also store that instant associated with an offset. The stored offset
> should always be the stored value's exact difference from UTC.

Never been a fan of handling offsets, per se. That's always a view
concern. In web it is easy enough to convert to local time zone. Even
in a report, I expect it would be easy to receive a time zone in order
to the Utc to local conversion in the handshake.

> So, provided that you grab the local time and the local offset currently in
> effect at that time, when the DTO is constructed, it can always be converted
> to an unambiguous UTC value without knowing the geopolitical time zone
> information.
>
> For the opposite transform, that is, converting a DTO to a local time and
> local offset appropriate to a specific region at that point in time, you
> would need to specify what time zone information to use, obviously. You
> don't need to record the time zone name unless you expressly need to display
> that information later - but I didn't see you do that in your Instant class.
>
> If you ask the computer for the current local DTO, it will return different
> offset depending on if DST is in effect or not. Regardless, the recorded
> value can be unambiguously converted to correct UTC.
>
> You can also have a DTO, add an hour to it, and get a new DTO which
> represents a point in time exactly one hour later - BUT it might not be a
> correctly expressed local time in your intended time zone, because it won't
> update the offset. For that you need help from a time zone. If it's the
> current timezone in effect, it should be easily achieved by just
> roundtripping the DTO to UTC and back.
>
>
> What WILL get you into trouble is if you store the local time _without_
> offset or timezone moniker (which should both change between DST and
> regular), because then some values will be ambiguous. You can't tell if it
> was the last hour of DST or the first hour of standard time.
>
> This is my understanding anyway. Happy to be corrected if I'm wrong.

Agreed. In any event, I always steer clear of this confusion whenever
possible. But what do I know.

> email to nhusers+u...@googlegroups.com.

> To post to this group, send email to nhu...@googlegroups.com.
> Visit this group at https://groups.google.com/group/nhusers.
> For more options, visit https://groups.google.com/d/optout.
>
>

> --
> You received this message because you are subscribed to a topic in the
> Google Groups "nhusers" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/nhusers/f8qa0bYPHWM/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> nhusers+u...@googlegroups.com.

[Oskar Berggren]

Den 26 jan. 2017 2:11 em skrev "Michael Powell" <mwpow...@gmail.com>:

On Thu, Jan 26, 2017 at 5:20 AM, Oskar Berggren
<oskar.b...@gmail.com> wrote:
> No,  I believe you are mistaken.
>
> There is only one UTC. The UTC is never different. It's value is always the
> same everywhere.

This is exactly correct and is why if you are storing potentially
ambiguous date/time, you are asking for trouble.

> The DateTimeOffset class is used to represent a specific point in time, and
> it can also store that instant associated with an offset. The stored offset
> should always be the stored value's exact difference from UTC.

Never been a fan of handling offsets, per se. That's always a view
concern. In web it is easy enough to convert to local time zone. Even
in a report, I expect it would be easy to receive a time zone in order
to the Utc to local conversion in the handshake.

I sort of agree. To my mind there are only two useful ways to store instants. Either as UTC, or as time+offset. The first is cleaner if you don't actually need to remember the offset other than for conversion to UTC, but one benefit from storing offset is that it makes it more visible that this is something you as the developer or DBA need to be mindful of. It's quite easy to forget the conversion otherwise, or accidentally storing local time without offset when it really should have been UTC.

/Oskar

> email to nhusers+unsubscribe@googlegroups.com.

> To post to this group, send email to nhu...@googlegroups.com.
> Visit this group at https://groups.google.com/group/nhusers.
> For more options, visit https://groups.google.com/d/optout.
>
>
> --

> You received this message because you are subscribed to a topic in the
> Google Groups "nhusers" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/nhusers/f8qa0bYPHWM/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to

> nhusers+unsubscribe@googlegroups.com.

> To post to this group, send email to nhu...@googlegroups.com.
> Visit this group at https://groups.google.com/group/nhusers.
> For more options, visit https://groups.google.com/d/optout.

--

You received this message because you are subscribed to the Google Groups "nhusers" group.

To unsubscribe from this group and stop receiving emails from it, send an email to nhusers+unsubscribe@googlegroups.com.

[Oskar Berggren]

I don't know if this is relevant to your problem anymore, but anyway:

The DateTimeOffset contains a time AND it's offset from UTC. Therefore the corresponding UTC time can be calculated easily by just subtracting the offset. This is accomplished by several methods available on the DTO class. It doesn't matter if the local time came from two different timezones with the same or different offsets. Calculating the UTC value for the same instant is unambiguous and doesn't require a timezone.

Suggest reading the documentation here:

https://msdn.microsoft.com/en-us/library/system.datetimeoffset(v=vs.110).aspx

And:

https://msdn.microsoft.com/en-us/library/system.datetimeoffset.touniversaltime(v=vs.110).aspx

https://msdn.microsoft.com/en-us/library/system.datetimeoffset.utcdatetime(v=vs.110).aspx

If you want to express the instant represented by a DTO as local time in some specific timezone, then yes, you would of course need to have the TimeZoneInfo for the target timezone.

This link is also useful. Among other things, it points out that DateTimeOffset is the most useful "default" type for representing instants in time:

https://msdn.microsoft.com/en-us/library/bb384267(v=vs.110).aspx

/Oskar

Den 26 jan. 2017 1:11 em skrev "Denis Pujdak" <denis....@gmail.com>:

DateTimeOffset contains a local time. There is no UTC (http://stackoverflow.com/questions/8343929/is-date-time-part-of-sql-servers-datetimeoffset-utc-or-local).

So if you don't know what timezone exactly is (-08:00 Pacific Time or -08:00 Baja California) you cannot get UTC correctly.

To unsubscribe from this group and stop receiving emails from it, send an email to nhusers+u...@googlegroups.com.

To post to this group, send email to nhu...@googlegroups.com.

Visit this group at https://groups.google.com/group/nhusers.
For more options, visit https://groups.google.com/d/optout.