Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to convert "string" to "timestamp without time zone"

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?

like image 670
Aijaz Chauhan Avatar asked Sep 20 '13 09:09

Aijaz Chauhan


People also ask

Is timestamp stored without timezone?

The timestamp datatype allows you to store both date and time. However, it does not have any time zone data.

What is timestamp without timezone in PostgreSQL?

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.

How do I cast a timestamp in PostgreSQL?

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


2 Answers

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:

  • Ignoring time zones altogether in Rails and PostgreSQL
like image 100
Erwin Brandstetter Avatar answered Sep 30 '22 13:09

Erwin Brandstetter


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; 
like image 42
Georgios Syngouroglou Avatar answered Sep 30 '22 12:09

Georgios Syngouroglou