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 been reading the Postgres documentation about timestamps, but I am still a little bit confused.

I need to store moments in times, measured in milliseconds, between the current time and midnight, January 1, 1970 UTC (like those defined in Java, but I am not using Java).

If I read the documentation properly, I believe I have to use CURRENT_TIMESTAMP, because it includes the timezone. Correct?

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

Motivation: I understand one could use something like EXTRACT on CURRENT_TIMESTAMP when selecting rows, but this conversion would be more expensive than storing the time in milliseconds. I am ready to store my moments into a bigint. It would be faster to compare moments or to compute time differences.

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
I take that there might not be such an impact on performance, but I feel much more comfortable using milliseconds. The solution you suggest works with seconds, not milliseconds, but I can live with that. –  JVerstry Feb 1 at 12:21
1  
@JVerstry: you are right, those are seconds. But you can just multiply the seconds with 1000 and you have milliseconds. I don't know what you mean with "more comfortable". I am much more comfortable with a value like 2007-08-24 18:34:01 than with 1187980441 –  a_horse_with_no_name Feb 1 at 12:26
    
I find it more comfortable, because comparing milliseconds (in my case) without the hassle of time zones is much simpler. –  JVerstry Mar 9 at 11:38

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.