News, examples, tips, ideas and plans.
Thoughts around ORM, .NET and SQL databases.

Thursday, September 22, 2016

Support for DateTimeOffset for PostgreSQL provider


We have introduced DateTimeOffset support for PostgreSQL provider in 5.0.11 RC. Here, we want to explain some restrictions of this RDBMS which affect our implementation of that type support.

We warn you that support for date/time with time zone is different from Oracle Database and Microsoft SQL Server implementations. The last two RDBMS has full support for all parts of date/time including time zone, but PostgreSQL doesn't.

If you want to use DateTimeOffset type in domain models on PostgreSQL storage we need to explain some things you may face with.

PostgreSQL has two types which can store both date and time - timestamp (timestamp without time zone) and timestamptz (timestamp with time zone). You may glance at them and think that everything is OK and you can store date/time with time zone in the last one. BUT, if you look carefully you will notice that values of both timestamp and timestamptz types have same size - 8 bytes. Strange, right?

Here is the quote from PostgreSQL documentation:
"All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client."
That means PostgreSQL does not store time zone part of given value at all. It will return you results in the time zone which was specified in PostgreSQL configuration file or the zone which was set manually for certain connection.

For instance, RDBMS instance was configured for time zone  UTC+07. You insert value '2016-09-10 18:19:22.000 +02:00' to some timestamptz column and query it back. The storage will return you '2016-09-10 23:19:22.000 +07:00' and it will do it all the time until you change server time zone.

Such odd implementation of timestamptz influences the results of queries you will receive using DataObjects.Net.

It will influence on date and time parts extraction. You will receive them for a timestamp value in server time zone. It is really important for such parts as Day, Hour, Minute, TimeOfDate and others which may be changed by switching time zone. Also you will get same offset for all DateTimeOffset values.

You also cannot use in LINQ queries methods like

- DateTimeOffset.ToOffset(TimeStamp offset);
- DateTimeOffset.ToLocalTime();
- DateTimeOffset.ToUniversalTime().

The reason is in another PostgreSQL restriction. It provides you a functionality of switching time zone for timestamp values by using  AT TIME ZONE statement or its equivalent - function  timezone(zone, timestamp). This table describes variants of  how it can be used. Note that, if you move a timestamptz value to certain time zone you will get a value without time zone . It makes it impossible to implement support for the methods I mentioned.

However, such type members like DateTimeOffset.LocalDateTime and DateTimeOffset.UtcDateTime will work, because they return values of DateTime type and such operations can be performed by AT TIME ZONE statement and these members are usable in queries.


These restrictions and particularities should be taken into account if you decide to use DateTimeOffset type in a domain model built on PostgreSQL storage. We would recommend you to set time zone in postgresql.config file or with SET statement for connection. It will prevent you from getting values with unexpected offsets. Also you will have to perform switching to time zones you need on client side.

We hope this text will help you avoid mistakes.

11 comments:

  1. I'm wondering how other ORM are solving this..

    ReplyDelete
  2. I'm wondering how other ORM are solving this..

    ReplyDelete

  3. Offersable - Cash Back Offers | Discount Coupons | Bank Offers | Promo Codes.
    Find best Discount Coupons, Cashback Offers, Hot Deals Bank Offers for all major e-commerce stores in the India



    Amazon cashback offers 2018Cashback Deals with sbi,hdfc,icici,axis banks,credit&debit card,
    Amazon Cashback Offers 2018 Upto 65% Cashback Deals, AMAZON CASHBACK OFFERS 2018, DISCOUNT COUPON OFFERS 2018, Amazon cashback offers with sbi hdfc icici axis banks credit&debit card offers more. amazon cashback offers2018,amazon india cashback,amazon offers on mobiles,amazon cashback2018,amazon offers 2018



    cashbackoffer amazon coupon code| amazon coupon promo code| amazon offer coupon code|
    amazon coupon code| amazon coupon promo code| amazon offer coupon code


    About Airport Parking,Accor HotelsAffordable Asia,Agoda,Air Asia Go,Akbar Travels,Amoma,Azores Getaways,Big Bus Tours,Booking,CarmelLimo,CatchThatBus,CheapAir,CheapTickets Singapore,Cleartrip,Ctrip English speaking,Etihad Airways,Expedia,Expedia India, Expedia Indonesia Hotels, Flights & Packages,Travel Coupons, Offers: Flights, Hotels, Bus Promo Codes

    CashBack Offers, Discount Coupons, Bank Offers & Promo Codes


    flipkart cashback offer,
    CashBack Offers, Discount Coupons


    Pepperfry, Mobikwik, Firstcry, Fab Hotels, Ebay, Aliexpress, Adidas, Healthkart, Pepperfry, Shoppers Stop, FlipKart, Amazon, Lurap, Giftalove, Reebok, SBI Card, CoolWinks, Housejoy, Shine, Jaypore, netmeds, FabAlley, Prettysecrets, FlowerAura, Clovia, Shopclues, Printvenue, Firstcry, Travelguru, Myntra, Zivame, Jabong, Ferns N Petals,Healthkart, Pepperfry, Shoppers Stop, FlipKart, Amazon


    CashBack Offers, Discount Coupons, Bank Offers & Promo Codes


    CashBack Offers, Discount Coupons, Bank Offers & Promo Codes

    CashBack Offers, Discount Coupons, Bank Offers & Promo Codes

    ReplyDelete
  4. nice post.super article .iam follow your website.


    Flipkart CashBack Offers-SBI, HDFC, ICICI, Citibank Cards&Flipkart HDFC Cards Offers"
    flipkart bank offers,flipkart cash back coupons,flipkart offers 2018,flipkart coupons 2018,flipkart cashback offers 2018,flipkart cashback offers,flipkart cash back offers,flipkart cashback coupons,flipkart cashback,Flipkart Cashback Offers,Flipkart Bank Offers,Flipkart Deals 2018, flipkart upcoming offers on mobiles, hdfc credit card offers on flipkart 2018 flipkart sbi offer big billion day, flipkart hdfc offer, flipkart hdfc offer august 2018 sbi flipkart offer 2018 flipkart icici offer


    FLIPKART CITIBANK OFFER,FLIPKART ICICI OFFER,
    SBI CARD OFFERS ON FLIPKART,
    FLIPKART CASHBACK OFFER TODAY,FLIPKART CREDIT CARD OFFERS,
    ALL BANK CREDIT CARD OFFERS,HDFC CREDIT CARD OFFERS ON FLIPKART,
    AXIS BANK CREDIT CARD OFFERS ON FLIPKART,
    CITIBANK CREDIT CARD OFFERS ON FLIPKART,SBI FLIPKART OFFER



    Flipkart Cashback Offers||Flipkart Bank Offers||Flipkart Deals 2018||flipkart upcoming offers,"
    "Flipkart Cashback Offers || Flipkart Bank Offers || Flipkart Deals 2018 ||" "flipkart upcoming offers on mobiles, hdfc credit card offers on flipkart 2018, sbi credit card offers on flipkart today, flipkart hdfc offer terms and conditions, sbi flipkart offer 2018, flipkart cashback phonepe, flipkart hdfc offer august 2018, flipkart icici offer




    "cashback,vouchers,coupons,discounts,offers,deals,promo codes,&Cashback Offers"
    "cash back coupons "Coupons, Discounts & Offers on 1500+ Shopping Sites in India. Get Extra Cashback Everytime You Shop Online using Coupons of Amazon, Flipkart, Tata CLiQ, Nykaa etc."cashback, vouchers, coupons, discounts, offers, deals, promo codes,Coupons, Promo Codes & Cashback Offers

    flipkart is Offering Cashback Offers Flipkart hdfc cashback offers You will get upto 75% Discount

    Flipkart is Offering Cashback Offers Flipkart SBI Cashback Offer You will get upto 75% Discount

    Flipkart is Offering Cashback Offers Flipkart AXIS Cashback Offer You will get upto 75% Discount

    Flipkart is Offering Cashback Offers Flipkart YES Cashback Offer You will get upto 75% Discount

    Flipkart is one of the biggest world wide eCommerce Site so here you will getFlipkart big billion day offers

    Flipkart is one of the biggest world wide eCommerce Site so here you will getflipkart bank cashback offersso

    ReplyDelete
  5. Thank you very much for this nice and informative post.To be honest I was planning to write about this topic and I intend to post a link to this wonderful article once I have it done.I learned myself few other things I never thought about.

    visit
    click here
    website
    blog

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete