Início / dba / Perguntas / 2758
Accepted
Jonas
Asked: 2011-05-18 01:16:03 +0800 CST

Como obter a coluna timestamp em apenas milissegundos do PostgreSQL?

  • 772

Tenho uma coluna "criada" com tipo timestamp without time zone default now()em um banco de dados PostgreSQL.

Se eu selecionar colums, ele terá um formato agradável e legível por padrão:

SELECT created FROM mytable;

         created
---------------------------
2011-05-17 10:40:28.876944

Mas eu gostaria de obter o timestamp em apenas milissegundos (como um Long). Algo assim:

SELECT meuformato(criado) FROM minhatabela;

     created
-----------------
2432432343876944

Como posso obter a coluna timestamp em apenas milissegundos do PostgreSQL?

Resposta a Jaque:

Eu recebo a mesma diferença que você (-3600), mas se eu usar timestamp with time zoneposso ver que o "erro" ou diferença é porque '1970-01-01' obtém fuso horário +01.

create table my_table_2(created timestamp with time zone);
CREATE TABLE
insert into my_table_2 (created) values (now()), ('1970-01-01');
INSERT 0 2
select created, extract(epoch from created) from my_table_2;
            created            |    date_part
-------------------------------+------------------
 2011-05-18 11:03:16.909338+02 | 1305709396.90934
 1970-01-01 00:00:00+01        |            -3600
(2 rows)

A diferença é um bug? Eu posso ser por causa do "horário de verão" no momento?

Também interessante ao usar to_timestamp()para inserir timestamp 0 e 1.

insert into my_table_2 (created) values (to_timestamp(0));
INSERT 0 1

insert into my_table_2 (created) values (to_timestamp(1));
INSERT 0 1
select created, extract(epoch from created) from my_table_2;
            created            |    date_part
-------------------------------+------------------
 2011-05-18 11:03:16.909338+02 | 1305709396.90934
 1970-01-01 00:00:00+01        |            -3600
 1970-01-01 01:00:00+01        |                0
 1970-01-01 01:00:01+01        |                1
postgresql timestamp

3 respostas

  1. Best Answer

    Use EXTRACTe o UNIX-Timestamp

    SELECT EXTRACT(EPOCH FROM TIMESTAMP '2011-05-17 10:40:28.876944') * 1000;

    daria

    1305621628876.94

    Multiplique por 1000para transformá-lo em milissegundos. Você pode então convertê-lo para o que quiser ( decimal seria uma boa escolha). Não se esqueça de manter o fuso horário em mente. JackPDouglas tem um exemplo em sua resposta . Aqui está um trecho de sua resposta ( createdsendo a coluna com seu timestamp) que ilustra como trabalhar com fusos horários:

    SELECT EXTRACT(EPOCH FROM created AT TIME ZONE 'UTC') FROM my_table;
    • 65

  2. --EDITAR--

    Eu descobri que isso (veja abaixo) é basicamente errado. Veja Como obtenho o timestamp unix atual do PostgreSQL? para a fonte da minha confusão...

    --FIM DA EDIÇÃO--

    Postando como resposta porque não funcionará como comentário.

    banco de teste:

    create role stack;
grant stack to dba;
create schema authorization stack;
set role stack;

create table my_table(created timestamp);
insert into my_table(created) values(now()),('1970-01-01');
\d my_table
              Table "stack.my_table"
 Column  |            Type             | Modifiers
---------+-----------------------------+-----------
 created | timestamp without time zone |

    consultas:

    select created, extract(epoch from created) from my_table;

          created          |    date_part
---------------------------+------------------
 2011-05-17 13:18:48.03266 | 1305634728.03266
 1970-01-01 00:00:00       |            -3600


select created, extract(epoch from date_trunc('milliseconds', created)) 
from my_table;

          created          |    date_part
---------------------------+------------------
 2011-05-17 13:18:48.03266 | 1305634728.03266
 1970-01-01 00:00:00       |            -3600


select created, extract(epoch from created at time zone 'UTC') from my_table;

          created          |    date_part
---------------------------+------------------
 2011-05-17 13:18:48.03266 | 1305638328.03266
 1970-01-01 00:00:00       |                0

    nota date_partna terceira consulta é: 130563 83 28.03266 - 3600 diferente.

    • 7

  3. Eu sei que este é um post antigo, mas vendo que eu tinha a mesma pergunta agora e estava referenciando coisas como este post, talvez minha resposta possa ser valiosa para outras pessoas:

    Anexe isso à coluna com o timestamp que você está consultando com isto: ::timestamptz(3)

    Isso filtrará a precisão para apenas recuperar/corresponder milissegundos -->

    Então, por exemplo, minha solução SQL em JS, agora funcionando, se parece com isso:

    `SELECT * FROM public.reports WHERE id::timestamptz(3) = '${dateRequested}';`
    • 2

relate perguntas