Using LLBLGen 2.6, Npgsql 2.0.6

I'm sure this is some thing easy that I'm just missing somewhere.

I have a timestamp: record_timestamp timestamp with time zone,

I see it has type timestamptz in the .lgp.

When I write set the timestamp, it's always using the time zone of the server, not the client. Apparently because I am not sending my local timezone (below).

What am I missing?

2011-09-16 14:15:31 PDT LOG: statement: UPDATE "public"."report_entries" SET "record_timestamp"=((E'2011-09-16 17:20:37.361317')::timestamp),"percent_through_wall"=((NULL)::int4),"indication_code"=((E'EDT')::text),"utility_field1"=((E'!UTC')::text) WHERE ( "public"."report_entries"."report_entry_id" = ((242)::int4))

2011-09-16 14:16:40 PDT LOG: statement: UPDATE "public"."report_entries" SET "record_timestamp"=((E'2011-09-16 14:21:46.075910')::timestamp),"percent_through_wall"=((NULL)::int4),"indication_code"=((E'PDT')::text),"utility_field1"=((E'!UTC')::text) WHERE ( "public"."report_entries"."report_entry_id" = ((243)::int4))

How are you setting the time zone? (i.e. client side?) I see you are using timestamp and not timpestamptz.

I'm not setting the timezone. I guess I just assumed it would use my local timezone. I don't know why it says timezone not timezonetz in the pg log, because my .lgp says timezonetz.

So, could you point out to me how that is done? I see SET TIME ZONE LOCAL, would I call that with every adapter I create?

Would you please try the latest version of Npgsql?

I upgraded to 2.0.11.0 of Npgsql and I'm still getting timestamp instead of timestamptz in my queries.

Looking in my generated code, it all seems to think I want timestamp. But when I look in the LLBLGen Pro tool (Oct 9, 2009), the DB type column for AnalysisRecordTimstamp says timestamptz.

AnalysisRecordTimestamp | 29 | System.DateTime | analysis_record_timestamp | timestamptz | 0 | 0 | 0 | False | False | False | True | False

Various applicable bits of info:

From the Entity.cs file:

        /// <summary> The AnalysisRecordTimestamp property of the Entity ReportEntry<br/><br/>
        /// </summary>
        /// <remarks>Mapped on  table field: "report_entries"."analysis_record_timestamp"<br/>
        /// Table field type characteristics (type, precision, scale, length): Timestamp, 0, 0, 0<br/>
        /// Table field behavior characteristics (is nullable, is PK, is identity): true, false, false</remarks>
        public virtual Nullable<System.DateTime> AnalysisRecordTimestamp
        {
            get { return (Nullable<System.DateTime>)GetValue((int)ReportEntryFieldIndex.AnalysisRecordTimestamp, false); }
            set { SetValue((int)ReportEntryFieldIndex.AnalysisRecordTimestamp, value); }
        }

From FieldInfoProvider.cs

            base.AddElementFieldInfo("ReportEntryEntity", "AnalysisRecordTimestamp", typeof(Nullable<System.DateTime>), false, false, false, true,  (int)ReportEntryFieldIndex.AnalysisRecordTimestamp, 0, 0, 0);

From PersistenceInfoProvider.cs

            base.AddElementFieldMapping( "ReportEntryEntity", "AnalysisRecordTimestamp", "analysis_record_timestamp", true, (int)NpgsqlDbType.Timestamp, 0, 0, 0, false, "", null, typeof(System.DateTime), 28 );

Ok. I looked deeper into this. Basically the problem is that npgsql maps timestamptz type to .Net DateTime type. See this for more information: http://llblgen.com/tinyforum/Messages.aspx?ThreadID=20031 (this is for EF, but expose the same problem in ngpsql).

This is unfortunate as we can't do much on LLBLGen side, it's a provider thing. Let's see what are your options:

A. Use a procedure. This is not ideal but you can create a procedure that receives a datetime and an timezone offset, then update the corresponding column.

B. Execute "SET TIME ZONE INTERVAL .." before your query. Example:

using (var adapter = new DataAccessAdapter())
{
    var queryCommand= new NpgsqlCommand("SET TIME ZONE INTERVAL '+10:00' HOUR TO MINUTE;");
    var query = new ActionQuery(queryCommand);                          
    adapter.ExecuteActionQuery(query);

    adapter.SaveEntity(payment);
}

The problem is that npgsql won't insert that offset in your field (+10). As the value is sent as DateTime it only uses the "SET TIME ZONE ..." to do the conversion in the connection session. In other words, that is useful to indicate what offset to use when converting the value, but the database value will be stored with the database timezone (i.e. -5).

C. Realize that you cannot sent a datetime format with timezone, then live with it and do the conversion in client side.

Finally got a chance to implement this, went with option B. Thanks!

      public static void AdjustNewAdapter(DataAccessAdapter adapter)
      {
         var offset = TimeZone.CurrentTimeZone.GetUtcOffset(DateTime.Now);
         var queryCommand = new NpgsqlCommand(string.Format("SET TIME ZONE INTERVAL '{0}' HOUR TO MINUTE;", offset));
         var query = new ActionQuery(queryCommand);
         adapter.ExecuteActionQuery(query);
      }

I was tempted to cache the offset, but I've fixed too many "I was running while daylight savings time toggled" bugs to be worth the time savings.