Description

The FORMAT_TIMESTAMP() returns the timestamp input value as a formatted string in the specified timezone. VoltDB stores timestamps as a time value in Greenwich Mean Time (GMT). The FORMAT_TIMESTAMP() function lets you return that value as a date and time string in a different timezone. You can specify the timezone as a either an offset of GMT or as a region as described by the Internet Assigned Numbers Authority (IANA) time zone database (tz). You can find a list of the IANA time zones on the Wikipedia tz page.

Time zone names are case sensitive. Time offsets are specified as a time value preceded by a plus or minus sign. The offset time value can be specified in hours (one or two digits); hours and minutes (four digits); or hours, minutes, and seconds (six digits). You can optionally use colons to separate the time units. For example, all of the following offsets specify the same amount of time — a positive offset of five hours:

+5  
+05 
+0500 
+05:00 
+050000 
+05:00:00

Examples

The following example uses the FORMAT_TIMESTAMP() function to return a timestamp column as an Eastern United States timezone date and time:

SELECT FORMAT_TIMESTAMP(e_time,'America/New_York'), e_log FROM event;

The next example uses an offset to go back 15 minutes:

SELECT FORMAT_TIMESTAMP(alarm.expires,'-00:15') AS warning FROM alarm;

The last example uses the FORMAT_TIMESTAMP() function to return timestamp values in the customer's chosen timezone.

CREATE PROCEDURE get_reservation AS
  SELECT r.id, 
   FORMAT_TIMESTAMP(r.departure,c.timezone) AS departure,
   FORMAT_TIMESTAMP(r.arrival,c.timezone) AS arrival
   FROM reservation AS r, customer AS c
   WHERE r.id = ?;