Discard Millisecond Part from Timestamp

Discard millisecond part from timestamp

A cast to timestamp(0) or timestamptz(0) rounds to full seconds:

SELECT now()::timestamp(0);

Fractions are not stored in table columns of this type.

date_trunc() truncates (leaves seconds unchanged) - which is often what you really want:

SELECT date_trunc('second', now()::timestamp);

I want to remove milliseconds in the timestamp, and I want to convert the string to the timestamp in DataStage

Is the data coming from file?
Then read it as varchar and then use substring function.
Eg: inpcolumn[1,19]

If data is coming from database then first use 'timestamp to string" function and then use substring function accordingly.

How to remove milliseconds from a timestamp?

If I understand you correctly there is no need to use Date / Calendar...

long yourmilliseconds = 1274883865399L;
long droppedMillis = 1000 * (yourmilliseconds/ 1000);    
System.out.println(droppedMillis);

1274883865000

Or... if you wish to have date formatting...

Calendar c = Calendar.getInstance();
c.setTime(new Date(yourmilliseconds));
c.set(Calendar.MILLISECOND, 0);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm.ss.SSS'Z'");
sdf.setTimeZone(TimeZone.getTimeZone("GMT"));
System.out.println(sdf.format(c.getTime()));

2010-05-26T14:24.25.000Z

Remove millisecond part of created_at in SQL to compare with other datetime

you could use string comparisons by converting the dates to strings

to_char( mydate, 'rrrrmmddhh24ss' ) 

remove milliseconds from Datetime in TZ format

You could drop the miliseconds part from the string, and then convert:

as.POSIXct(gsub("\\.[0-9]+Z", "", timestamp), 
           format="%Y-%m-%dT%H:%M:%S", tz="GMT")

How to remove milliseconds in timestamp spark sql

As your timestamp value is string and you are casting it to timestamp, you can try it using substring function.

Second option :

spark.sql("select from_unixtime(unix_timestamp(datecol, 'yyyy-MM-dd HH:mm:ss.SSS'),'yyyy-MM-dd HH:mm:ss') from table")

You were not providing the input format, that may be the reason you are getting the error.

I hope, this will work.

Thanks,
Manu

postgresql ignore milliseconds from timestamp when doing a select statement

You can use the date_trunc function to achieve this:

select date_trunc('second', time_captured) from server_perf;

see http://www.postgresql.org/docs/9.1/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC for more on this.

---

Related Topics