0

Is there a more succinct way to write the following in Postgres:

DATE(NOW() AT TIME ZONE time_zone)

i.e. to get the local time for a record with time zone time_zone?

Ideally I'd like to do something like this:

NOW(time_zone)

That doesn't work but is there anything similar and clean I can use?

Peter Nixey
  • 16,187
  • 14
  • 79
  • 133
  • 1
    You can easily write such a function –  Nov 18 '13 at 23:35
  • Thanks for that - you answered exactly what I asked. I was actually wanting to use it in the context of Rails (which I didn't mention) so have also added a link to a question which shows how to add in calculated attributes. Thank you. – Peter Nixey Nov 20 '13 at 17:10

1 Answers1

0
=> SELECT DATE(NOW() AT TIME ZONE 'UTC');
2013-11-19

=> SELECT DATE(NOW() AT TIME ZONE 'PST');
2013-11-18

UPDATE:

If you're looking to abstract this query into a custom function, you can create one as follows:

=> CREATE FUNCTION date_with_time_zone() RETURNS date AS '
     SELECT DATE(NOW() AT TIME ZONE ''PST'');
     ' LANGUAGE SQL;
CREATE FUNCTION

=> SELECT date_with_time_zone();
2013-11-18

While you still need to issue the SELECT command, the current date with time zone will be returned simply by invoking the function, rather than the entire query.

zeantsoi
  • 25,857
  • 7
  • 69
  • 61
  • Thanks for offering this. It's the same as the original one I posted though no? I'm trying to make it less verbose as it's making the SQL in a larger query (even more) complex to read. – Peter Nixey Nov 19 '13 at 00:29