DateTime mapping to Date Type in Postgres

Posts   
 
    
joenathan
User
Posts: 27
Joined: 28-Jun-2011
# Posted on: 08-Aug-2011 16:40:59   

When i create from the model first paradigm, and create a new System.DateTime field in an entity, the resultant postgres SQL that's generated makes that field a Date type instead of the psql timestamp type. Naturally the date type doesn't contain the time, so I can't get the resolution that I need. Is there any way to get this type to change to a psql timestamp that I don't know about or is this a bug?

Also I'm using LLBLGEN June 17th build 3.1 and NPGSQL 2.0.11.91

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 09-Aug-2011 06:42:51   

LLBLGen can manages a lot of PgSqlDbTypes (Date, Timestamp, Time, Timetz, Timestamptz). To specify this in model-first, you should:

  1. Open the entity for edition (Ctrl+Shift+O)
  2. Go to Field Mappings sub-tab
  3. Select the involved field. Below you will see the db field information for that field.
  4. Click the Edit field button.
  5. From DB Type combobox select the type that better accommodates to your needs (timestamp in your case). Click OK.
David Elizondo | LLBLGen Support Team
joenathan
User
Posts: 27
Joined: 28-Jun-2011
# Posted on: 15-Aug-2011 17:47:42   

Thanks a bunch that was it simple_smile

Engo
User
Posts: 5
Joined: 05-Feb-2012
# Posted on: 05-Feb-2012 16:10:11   

Is there a possibility to change the automatic mapping process to use timestamp für System.DateTime? It is a bit annoying to change this manually for a lot of entities.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 06-Feb-2012 03:02:08   

There is no way at this time you can define beforehand the dbType to use with some .net type. So you either change then manually or write a plugin to do it all at once.

David Elizondo | LLBLGen Support Team
Engo
User
Posts: 5
Joined: 05-Feb-2012
# Posted on: 06-Feb-2012 05:58:45   

Would it be possible to consider changing the internal mapping (PostgreSQL) from System.DateTime -> Date to System.DateTime -> Timestamp or Timestsamptz. I think the one with timezone information would be better, but everything is better than Date because it just leads to lost information.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 06-Feb-2012 11:18:54   

You can change this, but it requires you to change the code in the postgresql driver sourcecode (it's available to you in the sourcecode archive).

In PostgreSqlDBDriver.cs, there are two methods, FillNETToDBTypeConversionsList and FillDBTypeSortOrderList. The first one defines all model first mapping filters. These are used by the designer to find a DB type for a .NET type. You see there are 5 DB types mapped to DateTime, as that's the situation with npgsql: it maps 5 types to DateTime.

In FillDBTypeSortOrderList, the sort order is specified, which type is taken first. We defined the 'most common usage' types first, which is Date:


this.SortOrderPerDBType.Add((int)PgSqlDbTypes.Date, 0);
this.SortOrderPerDBType.Add((int)PgSqlDbTypes.Timestamp, 1);
this.SortOrderPerDBType.Add((int)PgSqlDbTypes.Timestamptz, 2);
this.SortOrderPerDBType.Add((int)PgSqlDbTypes.Time, 3);
this.SortOrderPerDBType.Add((int)PgSqlDbTypes.Timetz, 4);

If you change it into:


this.SortOrderPerDBType.Add((int)PgSqlDbTypes.Timestamptz, 0);
this.SortOrderPerDBType.Add((int)PgSqlDbTypes.Date, 1);
this.SortOrderPerDBType.Add((int)PgSqlDbTypes.Timestamp, 2);
this.SortOrderPerDBType.Add((int)PgSqlDbTypes.Time, 3);
this.SortOrderPerDBType.Add((int)PgSqlDbTypes.Timetz, 4);

it will pick Timestamptz always.

We didn't implement a UI system to configure this manually in the designer, as most of the time you don't need this as model first is basically about 'pick a DB type for me' and forget it.

Another workaround could be this: you work with Date in the model, you export DDL SQL script from the designer. Open the .sql file in a text editor and change the Date types to Timestamptz. Then run the script on the database server. Afterwards, refresh the catalog in the designer, which will change all Date types to Timestamptz. The model types stay the same as both are mapped to DateTime.

Frans Bouma | Lead developer LLBLGen Pro
Engo
User
Posts: 5
Joined: 05-Feb-2012
# Posted on: 06-Feb-2012 11:24:03   

Thank you for your answer. I will try your suggestion asap.