0

I have a date in milliseconds. obtained with something like:

SELECT EXTRACT(MILLISECONDS FROM NOW())

How do I revert to a date again?

I've tried with:

SELECT TO_TIMESTAMP(EXTRACT(MILLISECONDS FROM NOW()))

The above returns a date in 1970 ...

Thom Thom Thom
  • 1,279
  • 1
  • 11
  • 21
  • `extract(MILLISECONDS ...)` only returns the fractional seconds from your timestamp. You are looking for `extract(epoch ..)` –  Aug 19 '14 at 13:26
  • @a_horse_with_no_name: This is incorrect. I already posted the correct explanation from the manual. It is undefined what the OP actually wants. – Erwin Brandstetter Aug 19 '14 at 13:27
  • @ErwinBrandstetter: Thom apparently expects `extract(milliseconds)` to get a value that can then be converted back to the original value. The only way to do this is to use the `epoch` value. –  Aug 19 '14 at 13:28
  • I want exactly what @a_horse_with_no_name said. Convert a date to milliseconds and revert it later. – Thom Thom Thom Aug 19 '14 at 13:29
  • The JKS' answer is what you are looking for. –  Aug 19 '14 at 13:31
  • So do you have an epoch (which is the number of *seconds*, no milliseconds) or do you have a "date in milliseconds", which probably is supposed to mean "timestamp in milliseconds"? – Erwin Brandstetter Aug 19 '14 at 13:32
  • @a_horse_with_no_name: `extract(MILLISECONDS ...)` returns more than just the "fractional seconds". – Erwin Brandstetter Aug 19 '14 at 13:33
  • Erwin Brandstetter, I want to convert a date to milliseconds (not seconds). Then I want to revert it back to a date. – Thom Thom Thom Aug 19 '14 at 13:34
  • And you are aware of the difference between [`date` and `timestamp` or `timestamptz`](http://www.postgresql.org/docs/current/interactive/datatype-datetime.html)? `now()` returns `timestamptz`, not a "date". – Erwin Brandstetter Aug 19 '14 at 13:47

2 Answers2

1

EXTRACT doesn't get you date in milliseconds. I think that should work:

SELECT EXTRACT(EPOCH FROM NOW()) * 1000;
SELECT TO_TIMESTAMP(EXTRACT(EPOCH FROM NOW()))

Found here

Community
  • 1
  • 1
JKS
  • 339
  • 1
  • 12
1

EXTRACT(MILLISECONDS ...) gets you (per documentation) ...

The seconds field, including fractional parts, multiplied by 1000. Note that this includes full seconds.

Just test with:

SELECT now(), EXTRACT(MILLISECONDS FROM NOW()) AS millisec;

The rest is in the manual as well:

epoch

For timestamp with time zone values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative); for date and timestamp values, the number of seconds since 1970-01-01 00:00:00 local time; for interval values, the total number of seconds in the interval

SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');
*Result: 982384720.12*

SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
*Result: 442800*

Here is how you can convert an epoch value back to a time stamp:

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1 second';

(The to_timestamp function encapsulates the above conversion.)

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228