0

Trying to port a T-SQL CTE to PostgreSQL. In T-SQL cast(0 as datetime) returns 

1900-01-01 00:00:00.000

Is there an equivalent 'side-effect' in pg, to get Day One of the epoch?

EDIT:

to_timestamp(0) on my machine --returns 1969-12-31 19:00:00-05. 

select '1970-01-01 00:00:00'::timestamptz --returns 1970-01-01 00:00:00-05
select '1970-01-01 00:00:00'::timestamp --returns 1/1/1970 which is the same as
select '1970-01-01 00:00:00'::date    -- returns 1/1/1970

I expected the cast to timestamp to return 1970-01-01 00:00:00, that is, removing the timezone but not the time.

Tim
  • 8,669
  • 31
  • 105
  • 183

1 Answers1

0

If by epoch you mean 1 Jan 1970 00:00 (not 1900), you can do:

to_timestamp(0)

To get the time in a particular timezone, try:

to_timestamp(0) AT TIME ZONE 'GMT'

or

to_timestamp(0) AT TIME ZONE 'UTC'
mccannf
  • 16,619
  • 3
  • 51
  • 63
  • Thanks, that will do fine. – Tim Jan 30 '13 at 11:17
  • I am new to PostgreSQL, so maybe there's a setting I need to set before this will work, but when I do `to_timestamp(0)` it returns `1969-12-31 19:00:00-05`. So it's adjusting for timezone. Is there a way to return GMT? – Tim Jan 30 '13 at 11:26
  • Thanks. That returns 1/1/1970. – Tim Jan 30 '13 at 12:51