What's the Postgresql equivalent of string.Format("{0:s}", DateTime.Now) ?

What's the equivalent of sortable datetime/timestamp varchar in PostgreSQL?

Console.WriteLine("{0:s}", DateTime.Now);

sample format output:

2000-05-04T15:30:59

This works, SELECT now::varchar, output is '2009-03-25', but only for date type, wondering what's the equivalent for timestamp.

Note, i know date is sortable in and of itself, I just encounter a DateTime incompatibility between .NET and Mono, so i'll just transport(Remoting) date/timestamp types as varchar, underlying database type is still proper date/timestamp field type. For those who encounter this same problem, the work-around is to cast the date to varchar when retrieving the data, and casting the varchar back to date when saving.

From stackoverflow

• Basically you just use to_char function.

  The problem with your example, is that while theoretically this should work:

  select to_char( now(), 'YYYY-MM-DDTHH24:MI:SS' );
  

  In reality - PostgreSQL cannot "understand" that T is separator and HH24 is next "tag", and prints it as:

  2009-03-24THH24:32:45
  

  But you can change it relatively simply to:

  select translate( to_char( now(), 'YYYY-MM-DD HH24:MI:SS' ), ' ', 'T' );
  

  which outputs requested format:

  2009-03-24T21:33:21
  

  Of course remembering always to use translate gets old pretty fast, so instead you can create your own function and use it instead:

  create function custom_ts(timestamptz) returns text as $$
    select translate( to_char( $1, 'YYYY-MM-DD HH24:MI:SS' ), ' ', 'T' );
  $$ language sql;
  # select custom_ts(now());
        custom_ts
  ---------------------
   2009-03-24T21:35:47
  (1 row)
  

  Hao : hi depesz, first, thanks for the answer :-) i know you are a demigod with postgresql, i frequently read your blog. what's the concise expression for this comparison? http://stackoverflow.com/questions/680824/sql-equality-inequality-comparison-with-nullable-values

  depesz : Just like Milen wrote - IS DISTINCT FROM and possibly IS NOT DISTINCT FROM.

• Or simply:

  SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD"T"HH24:MI:SS');
  

  depesz : Great. Haven't noticed the "" trick in the docs.