0

showing the initial timestamp format

Currently I am working on metabase. I have my timestamp something like this 'Friday, June 23, 2017 8:43 AM'. I need to convert this timestamp from utc to ist form.

The code is something like this

SELECT CAST("public"."locations"."timestamp" AS date) AS "timestamp"
FROM "public"."locations"
GROUP BY CAST("public"."locations"."timestamp" AS date)
ORDER BY CAST("public"."locations"."timestamp" AS date) ASC
Scath
  • 3,777
  • 10
  • 29
  • 40
Addictd
  • 57
  • 1
  • 12

1 Answers1

0

In PostgreSQL1 and assuming a simplified scenario to concentrate on converting your timestamps to IST such is this one:

CREATE TABLE locations
(
    id serial PRIMARY KEY,
    your_timestamp timestamp with time zone
) ;

NOTE: don't use timestamp as a column name, to avoid confusions. timestamp is already a type.

INSERT INTO locations
    (your_timestamp)
VALUES
    ('2017-01-01 10:00:00 UTC'),
    ('2017-01-01 10:00:00 Europe/Rome'),
    ('2017-01-01 10:00:00 America/New_York'),
    ('2017-01-01 10:00:00 IST') ;

You'd convert all those times (not only the ones already entered as UTC, or with the local time zone of the client connecting to the database), you would use:

SELECT
    id, 
    your_timestamp,
    your_timestamp AT TIME ZONE 'UTC' AS timestamp_utc,
    your_timestamp AT TIME ZONE 'IST' AS timestamp_ist
FROM
    locations ;

id | your_timestamp         | timestamp_utc       | timestamp_ist      
-: | :--------------------- | :------------------ | :------------------
 1 | 2017-01-01 10:00:00+00 | 2017-01-01 10:00:00 | 2017-01-01 12:00:00
 2 | 2017-01-01 09:00:00+00 | 2017-01-01 09:00:00 | 2017-01-01 11:00:00
 3 | 2017-01-01 15:00:00+00 | 2017-01-01 15:00:00 | 2017-01-01 17:00:00
 4 | 2017-01-01 08:00:00+00 | 2017-01-01 08:00:00 | 2017-01-01 10:00:00

You can check it at dbfiddle here

References:

1 I don't know the MySQL equivalent. I hope you actually are using PostgreSQL, since your identifiers are "double quoted" and not `backtick quoted` as in MySQL.

joanolo
  • 6,028
  • 1
  • 29
  • 37
  • you didnt understood my question. i said queries are already made in the format shown(see linked imaeg) .i want to somehow use the existing query to change time to IST . thanku for the effort though – Addictd Jul 28 '17 at 19:46
  • Your original query *does what it does*. If you want to convert from one time zone to another, you need to do another thing: use `AT TIME ZONE 'IST'`. – joanolo Jul 28 '17 at 19:48