PostgreSQL compare a timestamp with timezone with a timestamp

postgresqltimestamp

How do you compare a timestamp with a timezone with a timestamp in postgresql?
I am trying to cast one of the 2 to the type of the other but it does not work for me. Eg.

Having

TS1: 2019-01-07 21:43:00

and

TS2: 2019-01-07 20:43:37+01

I need to compare them and check if they are the same not including the seconds. So I have tried with:

select attimestamp, attimestamp_tz, attimestam=attimestamp_tz::timestamp from attendance where atid=1070

but this will always return false.

Best Answer

create table test (ts1 timestamp without time zone, ts2 timestamp with time zone)

✓

insert into test (ts1, ts2) 
VALUES ('2019-01-07 21:43:00', '2019-01-07 20:43:37+01'), 
       ('2019-01-07 21:43:00', '2019-01-07 20:43:37-01')

2 rows affected

select *, 
       to_char(ts1, 'YYYYMMDDHH24MI') str_ts1, 
       to_char(ts2, 'YYYYMMDDHH24MI') str_ts2, 
       to_char(ts1, 'YYYYMMDDHH24MI') = to_char(ts2, 'YYYYMMDDHH24MI') compare 
from test

ts1                 | ts2                    | str_ts1      | str_ts2      | compare
:------------------ | :--------------------- | :----------- | :----------- | :------
2019-01-07 21:43:00 | 2019-01-07 19:43:37+00 | 201901072143 | 201901071943 | f      
2019-01-07 21:43:00 | 2019-01-07 21:43:37+00 | 201901072143 | 201901072143 | t

db<>fiddle here

Related Solutions

Postgresql – How to get the timestamp column in only milliseconds from PostgreSQL

Use EXTRACT and the UNIX-Timestamp

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2011-05-17 10:40:28.876944') * 1000;

would give

1305621628876.94

Multiply it by 1000 to turn it into milliseconds. You can then convert it to whatever you want (decimal would be a good choice). Don't forget to keep the timezone in mind. JackPDouglas has such an example in his answer. Here is an excerpt from his answer (created being the column with your timetamp) that illustrates how to work with timezones:

SELECT EXTRACT(EPOCH FROM created AT TIME ZONE 'UTC') FROM my_table;

Mysql – How to compare (in MySQL) a DATETIME value to a TIMESTAMP value

There is a manual page on Time and Date functions.

The best way to compare date and time and get minutes back is probably to use one of the following:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60
SELECT TIMESTAMPDIFF(MINUTE,timeb,timea)

Note that while TIMEDIFF works with times, TIMESTAMPDIFF requires a date component - and the values are switched around for TIMESTAMPDIFF.

You can return hours with either of these:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60/60
SELECT TIMESTAMPDIFF(HOUR,timeb,timea)

Note that when using DATEDIFF, the value returned is a difference between the date components of the given time - thus, a value of 23:59 on one day compared to a value of 0:01 on the next (a 2 minute difference) results in a date difference of 1 (a full day).

To get a difference between two times in 24-hour periods (length of a day) first calculate the difference in minutes then divide by the proper values to find the number of 24-hour periods. Try this:

SELECT TRUNCATE(TIMESTAMPDIFF(MINUTE,timeb,timea)/60.0/24.0,0)

This truncates the decimal portion, returning only the number of complete 24-hour periods in the specified timespan.

Best Answer

Related Solutions

Postgresql – How to get the timestamp column in only milliseconds from PostgreSQL

Mysql – How to compare (in MySQL) a DATETIME value to a TIMESTAMP value

Related Question