How do I convert date and seconds fields to a timestamp field in PostgreSQL?

Question

I have a date field, which is just text, and a field showing seconds since midnight.

CREATE TABLE t ("s" text, "d" text, "ts" timestamp)
;   
INSERT INTO t ("s", "d")
VALUES
    (24855, 20130604),
    (24937.7, 20130604)
;

How can I convert these fields into a timestamp, so I can run time-based queries?

See a demo with SQL Fiddle: http://sqlfiddle.com/#!15/67018/2

Erwin Brandstetter · Accepted Answer · 2013-12-10 22:34:49Z

1

Since your data includes fractional seconds, you need to adjust for those:

UPDATE t
SET    ts = to_timestamp(d||s, 'YYYYMMDDSSSS.MS');

Assuming milliseconds as the maximum precision. Else employ US for microseconds.

Consider the fine print in the manual:

In a conversion from string to timestamp, millisecond (MS) or microsecond (US) values are used as the seconds digits after the decimal point. For example to_timestamp('12:3', 'SS:MS') is not 3 milliseconds, but 300, because the conversion counts it as 12 + 0.3 seconds. This means for the format SS:MS, the input values 12:3, 12:30, and 12:300 specify the same number of milliseconds. To get three milliseconds, one must use 12:003, which the conversion counts as 12 + 0.003 = 12.003 seconds.

Also note that to_timestamp() returns timestamp with time zone, which is coerced to timestamp in the context of the update, which turns out all right. For other contexts, you may want to cast explicitly or use this alternative, yielding timestamp:

SELECT d::date + interval '1s' * s::numeric AS ts FROM t;

The plain cast to date (d::date) works because your dates are in standard ISO 8601 format - or so I assume.

edited Dec 10, 2013 at 22:34

answered Dec 10, 2013 at 22:29

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Martin Burch Over a year ago

Thank you for your more complete answer. I squinted at your SELECT statement for a minute before I noticed order of operations means 1 second would be multiplied by seconds before being added to date. Yes? I would feel safer with some parentheses.

Erwin Brandstetter Over a year ago

@MartinBurch: Yes, that's dictated by operator precedence. You can add parentheses for clarity, but the expression is valid as it is.

Martin Burch · Accepted Answer · 2013-12-10 21:12:20Z

1

Concatenate the two fields together and then use to_timestamp()

UPDATE t SET ts = to_timestamp(d||s, 'YYYYMMDDSSSS');

For a discussion of the formatting string used in to_timestamp, see Table 9-21: Template Patterns for Date/Time Formatting in the PostgreSQL documentation.

answered Dec 10, 2013 at 21:12

Martin Burch

3,0104 gold badges34 silver badges60 bronze badges

Collectives™ on Stack Overflow

How do I convert date and seconds fields to a timestamp field in PostgreSQL?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related