Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pmyXe-0006oJ-0q for pgsql-general@arkaria.postgresql.org; Thu, 13 Apr 2023 15:07:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pmyXc-0001LE-Vo for pgsql-general@arkaria.postgresql.org; Thu, 13 Apr 2023 15:07:16 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pmyXc-0001L5-IN for pgsql-general@lists.postgresql.org; Thu, 13 Apr 2023 15:07:16 +0000 Received: from mail-yb1-xb34.google.com ([2607:f8b0:4864:20::b34]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1pmyXY-002fBg-4u for pgsql-general@lists.postgresql.org; Thu, 13 Apr 2023 15:07:15 +0000 Received: by mail-yb1-xb34.google.com with SMTP id h198so22214511ybg.12 for ; Thu, 13 Apr 2023 08:07:11 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1681398430; x=1683990430; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=ToVUpcMGUtjPQqOD0WBRYpKHGOqJdlDxl5ev9TlPeow=; b=VYWxciEMfRf0iF8saqoj94wfgQ6eihnkRmpJFICb3t0hcg/DgZa1X0yGxx/lHMplcg YKIF43UAeQtI6roVqXJqgv4Q3zZBcdmiCOKC/KYNEpJD7UjP+3G8b70YhEhEqgYLnuwd RdMAYNT0a77Sztx0QMw8sp2RDjsfVGAoa8ch3dMK4ectkviW/D5MRvhHhNc6347RMf1W xGt7Ykk/w/5UwPblJPBBjfL5UbHFeTYBW0iBBOqLzNBwSaVhMBJ9FR47fhtC87NxFmZe MM2RDuBGRn7bz2srvMjiB+naCab4gPX+PZXc7VKc3JO1O6VTL/7ManWrdpWYNITaSNOi t4Mg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1681398430; x=1683990430; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=ToVUpcMGUtjPQqOD0WBRYpKHGOqJdlDxl5ev9TlPeow=; b=Yoof148vHL5JHdi1AauuVG4he7+6JKd6kB5nTzdzFTHbpWRKkaZBhM2bgNJxTCxVOy T4/YWjdEqme4gudtWeG+6LMiZw1fjhk1xGUAzY/c7P7nYLbblSofnFzSq5Ra4qm+U29g KelM6nlrFtjLV0tmBmvJVUjPEWQZ0GCK0R5E+b0txZbIeD5IP3DmCXNBj7Ob2ccIcbE3 pAJijkiTGSR4FDgpPJXaWkna97U1DqCbxSikdqgDWvz0avKIUY/qgpBLn7BiBrk5a729 dUWtwJwxm9arMiZEY/IDVyxgxNoguw50xuLMe+GpqFusVhS15qyk5Xqx99lOPgPHsWKb aFNA== X-Gm-Message-State: AAQBX9db7+JxOMPTikBzeZ1GjGNKveistyw/2WNZsdtOxx3+FmDH5b5i X9jbEppsMVLWZixNj20n6D44AzaGmC4= X-Google-Smtp-Source: AKy350bA/vdeRufe7TrSd2Wz7sDndtB7luOad8M4wiLbi0CYMTy9ktmJT3PB1zy2jruS3M3XHFk5gg== X-Received: by 2002:a05:6902:1205:b0:b8f:3842:8be0 with SMTP id s5-20020a056902120500b00b8f38428be0mr3352577ybu.43.1681398430081; Thu, 13 Apr 2023 08:07:10 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:69ef:db13:6221:13cf? ([2600:1700:bd2c:b810:69ef:db13:6221:13cf]) by smtp.googlemail.com with ESMTPSA id bw11-20020a056902160b00b00b7767ca748csm485409ybb.41.2023.04.13.08.07.09 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 13 Apr 2023 08:07:09 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------j1jKtgkpM90j0oZ2BKO2GaOQ" Message-ID: Date: Thu, 13 Apr 2023 10:07:09 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.9.0 Subject: Re: JSON / ASP.NET AJAX Dates support in PostgreSQL Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------j1jKtgkpM90j0oZ2BKO2GaOQ Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 4/13/23 09:44, Sebastien Flaesch wrote: > Hello, > > Is there an easy way to convert JSON data containing ASP.NET AJAX Dates > into PostgreSQL timestamp? > > I have this kind of JSON data: > > { >     "PurchaseOrder" : "4500000000", >     "CreationDate" : "\/Date(1672358400000)\/", >     "LastChangeDateTime" : "\/Date(1672692813062+0100)\/" > } > > Warning: Note the backslash before the slashes! > According to JSON spec this is valid JSON and used by AJAX Date format. > > > When loading that data in Firefox, the JSON visualizer shows AJAX Date > values as > > "/Date(1672358400000)/" > > and > > "/Date(1672692813062+0100)/" > > (without the backslash) > > My understanding is that backslash + slash = slash in JSON strings. This looks like "milliseconds since the Unix epoch: $ date -d @1672692813.062 Mon 02 Jan 2023 02:53:33 PM CST Thus: select to_timestamp(cast(1672692813062 as bigint))::timestamp; -- Born in Arizona, moved to Babylonia. --------------j1jKtgkpM90j0oZ2BKO2GaOQ Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 4/13/23 09:44, Sebastien Flaesch wrote:
Hello,

Is there an easy way to convert JSON data containing ASP.NET AJAX Dates into PostgreSQL timestamp?

I have this kind of JSON data:

{
    "PurchaseOrder" : "4500000000",
    "CreationDate" : "\/Date(1672358400000)\/",
    "LastChangeDateTime" : "\/Date(1672692813062+0100)\/"
}

Warning: Note the backslash before the slashes!
According to JSON spec this is valid JSON and used by AJAX Date format.


When loading that data in Firefox, the JSON visualizer shows AJAX Date values as

"/Date(1672358400000)/"

and

"/Date(1672692813062+0100)/"

(without the backslash)

My understanding is that backslash + slash = slash in JSON strings.

This looks like "milliseconds since the Unix epoch:

$ date -d @1672692813.062
Mon 02 Jan 2023 02:53:33 PM CST

Thus:
select to_timestamp(cast(1672692813062 as bigint))::timestamp;

--
Born in Arizona, moved to Babylonia.
--------------j1jKtgkpM90j0oZ2BKO2GaOQ--