Postgres timestamp field not showing microseconds component when value is all zeros2019 Community Moderator ElectionPostgreSQL: Select where timestamp is emptyUsing variables in SQLPSQL slower than PgAdmin IIIMake PostgreSQL timestamps display milliseconds even if zeroPostgreSQL JDBC Driver rounds crops double valuetrying to automatically timestamp postgresql table, ERROR: record “new” is not assigned yetpostgresql - losing command prompt after select from quoted table namePostgres insert adds empty values as ' ' - how to show blank insteadcopy timestamp value from one table to other table in postgresJOOQ timestamp field default value different from the db default value
What is the adequate fee for a reveal operation?
Welcoming 2019 Pi day: How to draw the letter π?
Why does a Star of David appear at a rally with Francisco Franco?
What is the significance behind "40 days" that often appears in the Bible?
What is the Japanese sound word for the clinking of money?
Why does overlay work only on the first tcolorbox?
A diagram about partial derivatives of f(x,y)
I am confused as to how the inverse of a certain function is found.
Is honey really a supersaturated solution? Does heating to un-crystalize redissolve it or melt it?
Professor being mistaken for a grad student
The German vowel “a” changes to the English “i”
Do the common programs (for example: "ls", "cat") in Linux and BSD come from the same source code?
"of which" is correct here?
Instead of a Universal Basic Income program, why not implement a "Universal Basic Needs" program?
Is there a symmetric-key algorithm which we can use for creating a signature?
Are ETF trackers fundamentally better than individual stocks?
How could an airship be repaired midflight?
How could a scammer know the apps on my phone / iTunes account?
Is there a place to find the pricing for things not mentioned in the PHB? (non-magical)
Violin - Can double stops be played when the strings are not next to each other?
Bacteria contamination inside a thermos bottle
Brexit - No Deal Rejection
My adviser wants to be the first author
What is "focus distance lower/upper" and how is it different from depth of field?
Postgres timestamp field not showing microseconds component when value is all zeros
2019 Community Moderator ElectionPostgreSQL: Select where timestamp is emptyUsing variables in SQLPSQL slower than PgAdmin IIIMake PostgreSQL timestamps display milliseconds even if zeroPostgreSQL JDBC Driver rounds crops double valuetrying to automatically timestamp postgresql table, ERROR: record “new” is not assigned yetpostgresql - losing command prompt after select from quoted table namePostgres insert adds empty values as ' ' - how to show blank insteadcopy timestamp value from one table to other table in postgresJOOQ timestamp field default value different from the db default value
Context: I'm using Postgres 9.3 and psql (PostgreSQL) 11.1.
Given the following table:
create table ts_test(ts timestamp);
The following insert commands display time with microsecond granularity:
INSERT INTO ts_test
VALUES
(now()),
(TIMESTAMP '2019-03-06 20:18:41.000001');
select * from ts_test;
ts
----------------------------
2019-03-06 20:40:35.062547
2019-03-06 20:18:41.000001
However, when a timestamp with all zeroes as the microsecond component is inserted, I observe the following behavior:
INSERT INTO ts_test VALUES (TIMESTAMP '2019-03-06 20:18:41.000000');
select * from ts_test;
ts
----------------------------
2019-03-06 20:40:35.062547
2019-03-06 20:18:41.000001
2019-03-06 20:18:41
I've pored over the documentation, but am drawing a total blank as to why the microseconds are omitted from the third entry.
postgresql psql
edited Mar 6 at 21:37
a_horse_with_no_name
303k46465561
asked Mar 6 at 20:45
Mr. SMr. S
8801822
add a comment |
Context: I'm using Postgres 9.3 and psql (PostgreSQL) 11.1.
Given the following table:
create table ts_test(ts timestamp);
The following insert commands display time with microsecond granularity:
INSERT INTO ts_test
VALUES
(now()),
(TIMESTAMP '2019-03-06 20:18:41.000001');
select * from ts_test;
ts
----------------------------
2019-03-06 20:40:35.062547
2019-03-06 20:18:41.000001
However, when a timestamp with all zeroes as the microsecond component is inserted, I observe the following behavior:
INSERT INTO ts_test VALUES (TIMESTAMP '2019-03-06 20:18:41.000000');
select * from ts_test;
ts
----------------------------
2019-03-06 20:40:35.062547
2019-03-06 20:18:41.000001
2019-03-06 20:18:41
I've pored over the documentation, but am drawing a total blank as to why the microseconds are omitted from the third entry.
postgresql psql
edited Mar 6 at 21:37
a_horse_with_no_name
303k46465561
asked Mar 6 at 20:45
Mr. SMr. S
8801822
I'm usingpsql (PostgreSQL) 11.1
– Mr. S
Mar 6 at 20:47
Aside, Postgres 9.3 reached EOL in November 2018. Consider upgrading: postgresql.org/support/versioning psql is only the command-line terminal.
– Erwin Brandstetter
Mar 7 at 1:06
add a comment |
Context: I'm using Postgres 9.3 and psql (PostgreSQL) 11.1.
Given the following table:
create table ts_test(ts timestamp);
The following insert commands display time with microsecond granularity:
INSERT INTO ts_test
VALUES
(now()),
(TIMESTAMP '2019-03-06 20:18:41.000001');
select * from ts_test;
ts
----------------------------
2019-03-06 20:40:35.062547
2019-03-06 20:18:41.000001
However, when a timestamp with all zeroes as the microsecond component is inserted, I observe the following behavior:
INSERT INTO ts_test VALUES (TIMESTAMP '2019-03-06 20:18:41.000000');
select * from ts_test;
ts
----------------------------
2019-03-06 20:40:35.062547
2019-03-06 20:18:41.000001
2019-03-06 20:18:41
I've pored over the documentation, but am drawing a total blank as to why the microseconds are omitted from the third entry.
postgresql psql
edited Mar 6 at 21:37
a_horse_with_no_name
303k46465561
asked Mar 6 at 20:45
Mr. SMr. S
8801822
Context: I'm using Postgres 9.3 and psql (PostgreSQL) 11.1.
Given the following table:
create table ts_test(ts timestamp);
The following insert commands display time with microsecond granularity:
INSERT INTO ts_test
VALUES
(now()),
(TIMESTAMP '2019-03-06 20:18:41.000001');
select * from ts_test;
ts
----------------------------
2019-03-06 20:40:35.062547
2019-03-06 20:18:41.000001
However, when a timestamp with all zeroes as the microsecond component is inserted, I observe the following behavior:
INSERT INTO ts_test VALUES (TIMESTAMP '2019-03-06 20:18:41.000000');
select * from ts_test;
ts
----------------------------
2019-03-06 20:40:35.062547
2019-03-06 20:18:41.000001
2019-03-06 20:18:41
I've pored over the documentation, but am drawing a total blank as to why the microseconds are omitted from the third entry.
postgresql psql
postgresql psql
edited Mar 6 at 21:37
a_horse_with_no_name
303k46465561
asked Mar 6 at 20:45
Mr. SMr. S
8801822
edited Mar 6 at 21:37
a_horse_with_no_name
303k46465561
asked Mar 6 at 20:45
Mr. SMr. S
8801822
edited Mar 6 at 21:37
a_horse_with_no_name
303k46465561
edited Mar 6 at 21:37
a_horse_with_no_name
303k46465561
edited Mar 6 at 21:37
a_horse_with_no_name
303k46465561
303k46465561
asked Mar 6 at 20:45
Mr. SMr. S
8801822
asked Mar 6 at 20:45
Mr. SMr. S
8801822
asked Mar 6 at 20:45
Mr. SMr. S
8801822
8801822
I'm usingpsql (PostgreSQL) 11.1
– Mr. S
Mar 6 at 20:47
Aside, Postgres 9.3 reached EOL in November 2018. Consider upgrading: postgresql.org/support/versioning psql is only the command-line terminal.
– Erwin Brandstetter
Mar 7 at 1:06
add a comment |
I'm usingpsql (PostgreSQL) 11.1
– Mr. S
Mar 6 at 20:47
Aside, Postgres 9.3 reached EOL in November 2018. Consider upgrading: postgresql.org/support/versioning psql is only the command-line terminal.
– Erwin Brandstetter
Mar 7 at 1:06
I'm using
psql (PostgreSQL) 11.1– Mr. S
Mar 6 at 20:47
I'm using
psql (PostgreSQL) 11.1– Mr. S
Mar 6 at 20:47
Aside, Postgres 9.3 reached EOL in November 2018. Consider upgrading: postgresql.org/support/versioning psql is only the command-line terminal.
– Erwin Brandstetter
Mar 7 at 1:06
Aside, Postgres 9.3 reached EOL in November 2018. Consider upgrading: postgresql.org/support/versioning psql is only the command-line terminal.
– Erwin Brandstetter
Mar 7 at 1:06
add a comment |
1 Answer
1
active
oldest
votes
TIMESTAMP '2019-03-06 20:18:41.000000' and TIMESTAMP '2019-03-06 20:18:41' are identical values. All-zero fractional digits, that's just insignificant noise which is not stored in the data type timestamp (internally an 8-byte integer).
If you want to preserve original literals including all noise, insignificant zeros and white space, you'll have to store it in a string type like text.
If you want to display timestamps with microseconds you might use to_char() with the template pattern US:
select to_char(ts, 'YYYY-MM-DD HH24:MI:SS:US') AS ts1 from ts_test;
ts1
----------------------------
2019-03-06 20:40:35.062547
2019-03-06 20:18:41.000001
2019-03-06 20:18:41.000000 -- !
This result is text, of course. (Use the pattern MS for milliseconds.)
answered Mar 7 at 1:03
Erwin BrandstetterErwin Brandstetter
351k68640819
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55031871%2fpostgres-timestamp-field-not-showing-microseconds-component-when-value-is-all-ze%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
TIMESTAMP '2019-03-06 20:18:41.000000' and TIMESTAMP '2019-03-06 20:18:41' are identical values. All-zero fractional digits, that's just insignificant noise which is not stored in the data type timestamp (internally an 8-byte integer).
If you want to preserve original literals including all noise, insignificant zeros and white space, you'll have to store it in a string type like text.
If you want to display timestamps with microseconds you might use to_char() with the template pattern US:
select to_char(ts, 'YYYY-MM-DD HH24:MI:SS:US') AS ts1 from ts_test;
ts1
----------------------------
2019-03-06 20:40:35.062547
2019-03-06 20:18:41.000001
2019-03-06 20:18:41.000000 -- !
This result is text, of course. (Use the pattern MS for milliseconds.)
answered Mar 7 at 1:03
Erwin BrandstetterErwin Brandstetter
351k68640819
add a comment |
TIMESTAMP '2019-03-06 20:18:41.000000' and TIMESTAMP '2019-03-06 20:18:41' are identical values. All-zero fractional digits, that's just insignificant noise which is not stored in the data type timestamp (internally an 8-byte integer).
If you want to preserve original literals including all noise, insignificant zeros and white space, you'll have to store it in a string type like text.
If you want to display timestamps with microseconds you might use to_char() with the template pattern US:
select to_char(ts, 'YYYY-MM-DD HH24:MI:SS:US') AS ts1 from ts_test;
ts1
----------------------------
2019-03-06 20:40:35.062547
2019-03-06 20:18:41.000001
2019-03-06 20:18:41.000000 -- !
This result is text, of course. (Use the pattern MS for milliseconds.)
answered Mar 7 at 1:03
Erwin BrandstetterErwin Brandstetter
351k68640819
add a comment |
TIMESTAMP '2019-03-06 20:18:41.000000' and TIMESTAMP '2019-03-06 20:18:41' are identical values. All-zero fractional digits, that's just insignificant noise which is not stored in the data type timestamp (internally an 8-byte integer).
If you want to preserve original literals including all noise, insignificant zeros and white space, you'll have to store it in a string type like text.
If you want to display timestamps with microseconds you might use to_char() with the template pattern US:
select to_char(ts, 'YYYY-MM-DD HH24:MI:SS:US') AS ts1 from ts_test;
ts1
----------------------------
2019-03-06 20:40:35.062547
2019-03-06 20:18:41.000001
2019-03-06 20:18:41.000000 -- !
This result is text, of course. (Use the pattern MS for milliseconds.)
answered Mar 7 at 1:03
Erwin BrandstetterErwin Brandstetter
351k68640819
TIMESTAMP '2019-03-06 20:18:41.000000' and TIMESTAMP '2019-03-06 20:18:41' are identical values. All-zero fractional digits, that's just insignificant noise which is not stored in the data type timestamp (internally an 8-byte integer).
If you want to preserve original literals including all noise, insignificant zeros and white space, you'll have to store it in a string type like text.
If you want to display timestamps with microseconds you might use to_char() with the template pattern US:
select to_char(ts, 'YYYY-MM-DD HH24:MI:SS:US') AS ts1 from ts_test;
ts1
----------------------------
2019-03-06 20:40:35.062547
2019-03-06 20:18:41.000001
2019-03-06 20:18:41.000000 -- !
This result is text, of course. (Use the pattern MS for milliseconds.)
answered Mar 7 at 1:03
Erwin BrandstetterErwin Brandstetter
351k68640819
answered Mar 7 at 1:03
Erwin BrandstetterErwin Brandstetter
351k68640819
answered Mar 7 at 1:03
Erwin BrandstetterErwin Brandstetter
351k68640819
answered Mar 7 at 1:03
Erwin BrandstetterErwin Brandstetter
351k68640819
351k68640819
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55031871%2fpostgres-timestamp-field-not-showing-microseconds-component-when-value-is-all-ze%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I'm using
psql (PostgreSQL) 11.1– Mr. S
Mar 6 at 20:47
Aside, Postgres 9.3 reached EOL in November 2018. Consider upgrading: postgresql.org/support/versioning psql is only the command-line terminal.
– Erwin Brandstetter
Mar 7 at 1:06