How to convert "string" to "timestamp without time zone"
Solution 1
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:
Solution 2
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;
Aijaz Chauhan
I am just new to .net and knowledge of .net is not much but whatever i have, i can use it very efficiently
Updated on May 04, 2021Comments
-
Aijaz Chauhan about 3 years
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 totimestamp without time zone
data type?