CODEWITHSUNDEEP
sqlpostgresql
user3206440
user3206440

Reputation: 5049

timestamp formatting - from 1/1000th of a sec to 1/100th of sec

Need to convert timestamps with 1/1000 second resolution to 1/100 resolution. I could possibly use to_char(timestamp, text) formatting function for this purpose, however need help with text to be used here.

input table (note - the timestamps here are stored as varchar)

+-------------------------+
|       ms1000_val        |
+-------------------------+
| 2017/02/20 08:27:17.899 |
| 2017/02/20 08:23:43.894 |
| 2017/02/20 08:24:41.894 |
| 2017/02/20 08:28:09.899 |
+-------------------------+

output table

+------------------------+
|       ms100_val        |
+------------------------+
| 2017/02/20 08:27:17.89 |
| 2017/02/20 08:23:43.89 |
| 2017/02/20 08:24:41.89 |
| 2017/02/20 08:28:09.89 |
+------------------------+

Upvotes: 2

Views: 206

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51456

You can specify it in brackets, like here:

t=# select now()::timestamp(2);
          now
------------------------
 2017-03-16 09:55:21.15
(1 row)

as OP noticed http://rextester.com/CBZ17212 produces different result then running in psql:

t=# CREATE TABLE Table1
t-#     ("ms1000_val" varchar(23))
t-# ;
CREATE TABLE
t=#
t=# INSERT INTO Table1
t-#     ("ms1000_val")
t-# VALUES
t-#     ('2017/02/20 08:27:17.892'),
t-#     ('2017/02/20 08:23:43.891'),
t-#     ('2017/02/20 08:24:41.897'),
t-#     ('2017/02/20 08:28:09.893')
t-# ;
INSERT 0 4
t=# select ms1000_val::timestamp(2) as time_formatted
t-#        from Table1;
     time_formatted
------------------------
 2017-02-20 08:27:17.89
 2017-02-20 08:23:43.89
 2017-02-20 08:24:41.9
 2017-02-20 08:28:09.89
(4 rows)

Upvotes: 6

Piotr Rogowski
Piotr Rogowski

Reputation: 3880

show this, i use to_char and substr and i get format yyyy/MM/dd ......

select substr(to_char(now(),'yyyy/MM/dd HH:mm:ss MS'),0,length('yyyy/MM/dd HH:mm:ss MS')+1);

Upvotes: 1

Related Questions