Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Designer> DateTimeOffset using PostgreSQL and timestamptz column type
 

Pages: 1
Designer
DateTimeOffset using PostgreSQL and timestamptz column type
Page:1/1 

  Print all messages in this thread  
Poster Message
bdavis
User



Location:

Joined on:
23-Jan-2019 13:46:45
Posted:
6 posts
# Posted on: 23-Jan-2019 13:57:00.  
I have a column in my table that is of data type 'timestamp with time zone' and I'm trying to map that to a .Net Type of 'DateTimeOffset'. However, when I try changing that in the Designer it states that I need a 'Type Converter' for this. When I click on the 'TypeConverter to use' column in the Field mappings section it shows <None>.

I'm using Designer Version 5.5 (5.5.0) RTM. Build Version 02-Nov-2018. And Npgsql version 4.0.4.

Can you please point me in the right direction?

Thanks so much!
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14464 posts
# Posted on: 24-Jan-2019 08:38:20.  
That's the default .NET Type returned by the NpgSql.

ref: https://www.npgsql.org/doc/types/datetime.html
and: https://www.npgsql.org/doc/types/basic.html


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37375 posts
# Posted on: 24-Jan-2019 11:07:42.  
Time with Timezone (timetz) has as default DateTimeOffset. Timestamp with timezone (Timestamptz) has 'DateTime' as default type. We map only default types so we map Timezone with Timestamp to DateTime as that's the default type for Timezone with timestamp.

DateTimeOffset however is a different kind of type, it represents a point in time relative to UTC. Because of this you need to convert the DateTime value that is returned by Npgsql into a DateTimeOffset (we only read the default GetValue() value, we don't read the GetFieldValue<T>() value which returns the alternative type in Npgsql).

This conversion between DateTime and DateTimeOffset has to be done in a type converter, and we don't ship that particular one with the framework so you have to create one yourself. This is documented here: https://www.llblgen.com/Documentation/5.5/SDK/gui_implementingtypeconverter.htm

As the whole time / timezone aspect of postgresql & Npgsql is rather confusing at times, just to be clear: you really want to convert it to DateTimeOffset?
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
bdavis
User



Location:

Joined on:
23-Jan-2019 13:46:45
Posted:
6 posts
# Posted on: 24-Jan-2019 13:50:11.  
So the reasoning behind wanting to use DateTimeOffset is that we are sending our dates/times via Web API to Angular applications. The time parts were always getting skewed when they were sent between the two (based off the timezone offset). To alleviate this, we started just sending the Unix Time in Milliseconds value as that is easily converted in Angular to the correct value we needed and easily went back into a DateTimeOffset object in our API.

Most of our databases are MS SQL, which has a DateTimeOffset datatype. We are moving some of our databases to PostgreSQL and needed a way to store the same data types. Timestamp with time zone seemed like the best fit, however it returns DateTime objects already converted to Local time. This can be a little confusing as it is stored as UTC in a column suffixed with Utc and then it comes back and is already converted to local time.


  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14464 posts
# Posted on: 24-Jan-2019 17:27:18.  
Maybe you need to use timetz (in Postgres) instead.
  Top
bdavis
User



Location:

Joined on:
23-Jan-2019 13:46:45
Posted:
6 posts
# Posted on: 30-Jan-2019 16:25:04.  
Alright, I'll try just converting the DateTime object to a DateTimeOffest. If that doesn't do what I need, I'll rework some of my logic.

Thanks!


  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.