I am new to Postgresql and I am using WCF services.
Here is my code snippet:
$.ajax({ url: '../Services/AuctionEntryServices.svc/InsertAuctionDetails', data: JSON.stringify({ "objAuctionEntryEntity": { "AuctionNO": '', "AuctionDate": $('[Id$="lblAuctionDateVal"]').text(), "TraderID": $('[Id$="ddlTraderName"] option:selected').val(), "Grade": $('[Id$="ddlGrade"] option:selected').val(), "Varity": $('[Id$="ddlVarity"] option:selected').val(), "QuntityInAuction": $('#txtQuantityForAuction').val(), "AuctionRate": $('#txtAuctionRate').val(), "BrokerID": a[0], "IsSold": $('#chlIsSold').is(':checked'), "CreatedBy": $.parseJSON(GetCookie('Admin_User_In_Mandi')).UserID, "UpdatedBy": $.parseJSON(GetCookie('Admin_User_In_Mandi')).UserID, "CreationDate": GetCurrentDate().toMSJSON(), "IsActive": true, "AuctionTransaction": arrAuctionTransaction, "MandiID": $.parseJSON(GetCookie('Admin_User_In_Mandi')).MandiID, "FarmerID": _ownerid, "AuctionNO": _auctionno, "AmmanatPattiID": _ammantpattiid, "ToTraderID": b[0], "ToTraderName": $('#txtOtherBuyerNameEN').val(), "ToTraderName_HI": $('#txtOtherBuyerNameHI').val() } }), type: 'POST', contentType: 'application/json', dataType: 'json' });
Here:
$('[Id$="lblAuctionDateVal"]').text() = "20/8/2013 14:52:49"
And my data type for this field is timestamp without time zone
.
How to convert this string to timestamp without time zone
data type?
The timestamp datatype allows you to store both date and time. However, it does not have any time zone data.
In a literal that has been determined to be timestamp without time zone , PostgreSQL will silently ignore any time zone indication. That is, the resulting value is derived from the date/time fields in the input value, and is not adjusted for time zone.
String to Date and Timestamp The TO_DATE function in PostgreSQL is used to converting strings into dates. Its syntax is TO_DATE(text, text) and the return type is date. The TO_TIMESTAMP function converts string data into timestamps with timezone. Its syntax is to_timestamp(text, text) .
String representation of a timestamp
(= timestamp without time zone
) depends on your locale settings. Therefore, to avoid ambiguities leading to data errors or Postgres coughing up an exception, you have two options:
1.) Use ISO 8601 format, which works the same with any locale or DateStyle
setting:
'2013-08-20 14:52:49'
You may have to cast the string literal explicitly where the data type cannot be derived from context, depending on the use case:
'2013-08-20 14:52:49'::timestamp
2.) Convert the string to timestamp
using to_timestamp()
with a matching template pattern:
to_timestamp('20/8/2013 14:52:49', 'DD/MM/YYYY hh24:mi:ss')
This returns timestamptz
, assuming the current timezone setting. Typically (like in an assigmment) the type is coerced accordingly. For timestamp
, this means that the time offset is truncated and you get the expected value. Again, if the target type cannot be derived from context, you may have to cast explicitly:
to_timestamp('20/8/2013 14:52:49', 'DD/MM/YYYY hh24:mi:ss')::timestamp
Since that simply strips the time offset, it results in the expected value. Or use the AT TIME ZONE
construct with a time zone of your choosing:
to_timestamp('20/8/2013 14:52:49', 'DD/MM/YYYY hh24:mi:ss') AT TIME ZONE 'UTC'
While the target time zone is the same as your current timezone
setting, no transformation takes place. Else the resulting timestamp is transposed accordingly. Further reading:
To convert a string into a timestamp without timezone, for Postgresql, I use the above
SELECT to_timestamp('23-11-1986 09:30:00', 'DD-MM-YYYY hh24:mi:ss')::timestamp without time zone;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With