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.

13 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. "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



    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



    flipkart sbi offers, Flipkart Sbi Offer, SBI offers on Flipkart, Sbi offers in Flipkart, Sbi offer flipkart, Sbi discounts flipkart, FLipkart sbi discounts

    MyDealsAndCoupons,flipkart cashback offers,cashbackcoupons,flipkartcashbackoffers,cashbackoffer,cashbackofferscoupons,flipkarthdfcoffer,latestcashbackoffers,flipkartcreditcardoffers,dealsncashbackcashbackoffers

    cashbackcoupons

    Amazon cashback offers today, Amazon cashback offers, Amazon cashback offers on mobiles,
    amazon cashback offers today,amazon cashback offers,amazon cashback offer august,amazon citibank cash back offer,amazon cashback offer on hdfc credit card,amazon cashback offer on hdfc credit card,amazon cashback offer jio,amazon cashback offers on mobiles,amazon pay cashback offer today,amazon offers 10 cashback




    Flipkart HDFC Offer 2018 | Flipkart Offers Today | Flipkart Bank Offer

    Flipkart CashBack Offers - SBI, HDFC, ICICI, Citibank Cards,flipkart sbi debit card emi,flipkart upcoming offers on mobiles,flipkart credit card,flipkart cashback phonepe,flipkart credit card generator,flipkart cashback offer phonepe,flipkart american express,india fake debit card generator,flipkart american express gift card,sbi credit card offers on flipkart today,sbi flipkart offer,
    sbi debit card offers cash back,sbi flipkart offer 2018,,telugu bloggers ,telugu websites

    ReplyDelete


  6. 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



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


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

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

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

    Flipkart is Offering Cashback Offers Flipkart offers today You will get upto 75% Discount

    Flipkart is Offering Cashback Offers Flipkart Today saree offers You will get upto 75% Discount

    ReplyDelete
  7. I have read your article, it is very informative and helpful for me.I admire the valuable information you offer in your articles. Thanks for posting it.

    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


    Flipkart cashback offers on mobiles,
    Flipkart cashback offers,
    Flipkart cashback coupons,
    Flipkart upcoming offers on mobiles,
    Flipkart HDFC cashback offers,
    Flipkart SBI cashback offers,
    Flipkart AXIS cashback offers,
    Flipkart Citibank cashback offers,
    Flipkart cashback on mobiles,
    Flipkart cashback offers today,
    Flipkart big billion day cashback offers,
    Flipkart big billion days cashback offers,
    big billion day cashback offers,
    Flipkart big billion days offers,
    flipkart cashback on mobiles,
    flipkart citibank offer,
    sbi credit card offers on flipkart today,
    flipkart upcoming offers on mobiles,
    hdfc credit card offers on flipkart,
    flipkart upcoming big billion day,
    flipkart upcoming sale,
    sbi credit card offers on mobiles,
    flipkart offers today,
    flipkart offers on mobiles,
    flipkart offers today mobile phones,
    flipkart cashback offers,
    flipkart cashback coupons,
    flipkart sale today offer,
    flipkart cashback offers today,
    cashback offers,
    flipkart cashback offer today

    ReplyDelete
  8. I have read your article, it is very informative and helpful for me.I admire the valuable information you offer in your articles. Thanks for posting it.

    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


    Flipkart cashback offers on mobiles,
    Flipkart cashback offers,
    Flipkart cashback coupons,
    Flipkart upcoming offers on mobiles,
    Flipkart HDFC cashback offers,
    Flipkart SBI cashback offers,
    Flipkart AXIS cashback offers,
    Flipkart Citibank cashback offers,
    Flipkart cashback on mobiles,
    Flipkart cashback offers today,
    Flipkart big billion day cashback offers,
    Flipkart big billion days cashback offers,
    big billion day cashback offers,
    Flipkart big billion days offers,
    flipkart cashback on mobiles,
    flipkart citibank offer,
    sbi credit card offers on flipkart today,
    flipkart upcoming offers on mobiles,
    hdfc credit card offers on flipkart,
    flipkart upcoming big billion day,
    flipkart upcoming sale,
    sbi credit card offers on mobiles,
    flipkart offers today,
    flipkart offers on mobiles,
    flipkart offers today mobile phones,
    flipkart cashback offers,
    flipkart cashback coupons,
    flipkart sale today offer,
    flipkart cashback offers today,
    cashback offers,
    flipkart cashback offer today

    ReplyDelete

  9. "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



    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



    flipkart sbi offers, Flipkart Sbi Offer, SBI offers on Flipkart, Sbi offers in Flipkart, Sbi offer flipkart, Sbi discounts flipkart, FLipkart sbi discounts

    MyDealsAndCoupons,flipkart cashback offers,cashbackcoupons,flipkartcashbackoffers,cashbackoffer,cashbackofferscoupons,flipkarthdfcoffer,latestcashbackoffers,flipkartcreditcardoffers,dealsncashbackcashbackoffers

    cashbackcoupons

    Amazon cashback offers today, Amazon cashback offers, Amazon cashback offers on mobiles,
    amazon cashback offers today,amazon cashback offers,amazon cashback offer august,amazon citibank cash back offer,amazon cashback offer on hdfc credit card,amazon cashback offer on hdfc credit card,amazon cashback offer jio,amazon cashback offers on mobiles,amazon pay cashback offer today,amazon offers 10 cashback




    Flipkart HDFC Offer 2018 | Flipkart Offers Today | Flipkart Bank Offer

    Flipkart CashBack Offers - SBI, HDFC, ICICI, Citibank Cards,flipkart sbi debit card emi,flipkart upcoming offers on mobiles,flipkart credit card,flipkart cashback phonepe,flipkart credit card generator,flipkart cashback offer phonepe,flipkart american express,india fake debit card generator,flipkart american express gift card,sbi credit card offers on flipkart today,sbi flipkart offer,
    sbi debit card offers cash back,sbi flipkart offer 2018,,telugu bloggers ,telugu websites

    ReplyDelete