Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a column "created" with type timestamp without time zone default now() in a PostgreSQL database.

If I select colums, it has a nice and readable format per default:

SELECT created FROM mytable;

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

But I would like to get the timestamp in only milliseconds (as a Long). Something like this:

SELECT myformat(created) FROM mytable;

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

How can I get the timestamp column in only milliseconds from PostgreSQL?


Response to Jack:

I do get the same difference as you (-3600), but if I use timestamp with time zone I can see that the "error" or difference is because '1970-01-01' gets time zone +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)

Is the difference a bug? I may be because of "Daylight saving times" at the moment?


Also interesting while using to_timestamp() to insert timestamp 0 and 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
share|improve this question
    
do you mean you want the number of milliseconds since 1970-01-01, or from some other baseline? –  Jack Douglas May 17 '11 at 11:06
    
@Jack: Yes I want it from 1970-01-01. I will use it to create a new Date(timeinmillis) in Java and JavaScript. –  Jonas May 17 '11 at 11:19
    
I think you have to watch out for time zone - see my comment on the accepted answer –  Jack Douglas May 17 '11 at 11:46
    
@Jack but my column is without time zone. So I think it should work. –  Jonas May 17 '11 at 11:54
    
@Jack: this works for me: select extract(epoch from date_trunc('milliseconds', now())) * 1000; –  Jonas May 17 '11 at 11:55

2 Answers 2

up vote 9 down vote accepted

Use EXTRACT and the UNIX-Timestamp

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

would give

1305621628876.94

Multiply it by 1000 to turn it into milliseconds. You can then convert it to whatever you want (decimal would be a good choice). Don't forget to keep the timezone in mind. JackPDouglas has such an example in his answer. Here is an excerpt from his answer (created beeing the column with your timetamp) that illustrates how to work with timezones:

SELECT EXTRACT(EPOCH FROM created AT TIME ZONE 'UTC') FROM my_table;
share|improve this answer
    
I think you need select extract(epoch from '2011-05-17 10:40:28.876944+00'::timestamp with time zone) * 1000;? SELECT EXTRACT(EPOCH FROM TIMESTAMP '1970-01-01 00:00:00') * 1000; gives -3600000 where I am. –  Jack Douglas May 17 '11 at 11:42
    
Thanks, I used this select extract(epoch from date_trunc('milliseconds', now())) * 1000; and it works. –  Jonas May 17 '11 at 11:53
    
@JackPDouglas Good info, wasn't really taking care of it. Updated the answer to point to your comment/answer. –  DrColossos May 17 '11 at 13:57
    
thanks, but better to just update your answer as it has been acepted? +1 in anticipation :) –  Jack Douglas May 17 '11 at 15:21
    

--EDIT--

I've discovered this (see below) is basically wrong. See How do I get the current unix timestamp from PostgreSQL? for the source of my confusion...

--END EDIT--

Posting as an answer because it won't work as a comment.

testbed:

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 |

queries:

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

note date_part in the third query is: 1305638328.03266 - 3600 different.

share|improve this answer
    
Do you mean that only the last query is correct? I always generate my values using now() so all my values will be like your 2011-05-17 13:18...-value. I will not insert dates without the milliseconds. When the value is generated with now() they seem to be the same on both queries. –  Jonas May 17 '11 at 12:50
    
You must be on UTC then? What does show timezone; give you in psql? –  Jack Douglas May 17 '11 at 13:21
    
@Jack: show timezone; gives me localtime. But I think that timestamps from now() always is stored in UTC. I'm in UTC+2. –  Jonas May 17 '11 at 13:24
    
now() is timestamp with time zone, so not stored in UTC or any other time zone. localtime is new to me - perhaps it means it uses your OS timezone? See this thread for a (possibly) helpful discussion –  Jack Douglas May 17 '11 at 15:20
    
@Jack: But you get the same timestamp as result in all your three queries, so I don't understand the difference between them, really. –  Jonas May 17 '11 at 15:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.