Postgresql – How to convert a unix time to PostgreSQL’ Timstamp without timezome

In postgres, timestamp with time zone can be abbreviated as timestamptz, and timestamp without time zone as timestamp. I will use the shorter type names for simplicity.

Getting the Unix timestamp from a postgres timestamptz like now() is simple, as you say, just:

select extract(epoch from now());

That's really all you need to know about getting the absolute time from anything of type timestamptz, including now().

Things only get complicated when you have a timestamp field.

When you put timestamptz data like now() into that field, it will first be converted to a particular timezone (either explicitly with at time zone or by converting to the session timezone) and the timezone information is discarded. It no longer refers to an absolute time. This is why you don't usually want to store timestamps as timestamp and would normally use timestamptz — maybe a film gets released at 6pm on a particular date in every timezone, that's the kind of use case.

If you only ever work in a single time zone you might get away with (mis)using timestamp. Conversion back to timestamptz is clever enough to cope with DST, and the timestamps are assumed, for conversion purposes, to be in the current time zone. Here's an example for GMT/BST:

select '2011-03-27 00:59:00.0+00'::timestamptz::timestamp::timestamptz
     , '2011-03-27 01:00:00.0+00'::timestamptz::timestamp::timestamptz;

/*
|timestamptz           |timestamptz           |
|:---------------------|:---------------------|
|2011-03-27 00:59:00+00|2011-03-27 02:00:00+01|
*/

_DBFiddle

But, note the following confusing behaviour:

set timezone to 0;

values(1, '1970-01-01 00:00:00+00'::timestamp::timestamptz)
    , (2, '1970-01-01 00:00:00+02'::timestamp::timestamptz);

/*
|column1|column2               |
|------:|:---------------------|
|      1|1970-01-01 00:00:00+00|
|      2|1970-01-01 00:00:00+00|
*/

_DBFiddle

This is because:

PostgreSQL never examines the content of a literal string before determining its type, and therefore will treat both […] as timestamp without time zone. To ensure that a literal is treated as timestamp with time zone, give it the correct explicit type…In a literal that has been determined to be timestamp without time zone, PostgreSQL will silently ignore any time zone indication

I understand one could use something like EXTRACT on CURRENT_TIMESTAMP when selecting rows

You don't need extract in order to get the data:

select *
from your_table
where timestamp_column >= timestamp '2007-08-24 18:34:01';

will happily use an index on moment_in_time and will be very efficient - in fact there will be no difference in performance compared to

select *
from your_table
where milliseconds_column >= 1187906400000;

Now look at the two statements and tell me for which one you immediately understand what the query will return.

If you do need those milliseconds in your application then you can always do something like this:

select some_col, 
       other_col,
       extract(epoch from timestamp_column) * 1000 as millis
from your_table
where timestamp_column >= timestamp '2007-08-24 18:34:01';

And again, I'm almost 100% certain that you won't see any performance difference to:

select some_col, 
       other_col,
       milliseconds_column
from your_table
where  milliseconds_column >= 1187906400000;

Plus: if you do store the milliseconds and you want to display the real date, you always need to apply an additional conversion:

select some_col, 
       other_col,
       to_char(timestamp_column, 'yyyy-mm-dd') as the_date
from your_table
where timestamp_column >= timestamp '2007-08-24 18:34:01';

vs.

select some_col, 
       other_col,
       to_char(to_timestamp(milliseconds_column / 1000), 'yyyy-mm-dd) as the_date
from your_table
where  milliseconds_column >= 1187906400000;

The second one one be slower than the first one, but it's more typing and makes the code harder to read.

Edit:

how to create a stored procedure that will convert this timestamp into the milliseconds I want (and the reverse function too)?

You don't need to write your own function:

1. convert timestamp to milliseconds:
   extract(epoch from current_timestamp) * 1000 + extract(milliseconds from current_timestamp)
2. convert milliseconds to timestamp: to_timestamp(1187980443530 / 1000)

So to wrap it up:

Go with a timestamp column and don't worry about performance.