Timestamp Transform

Data in Timestamp type can be converted to Decimal, Integer or String type. You can perform the following operations:

Add days

This operation adds an increment value to the day.

InputIncrementOutput
2009-12-27 00:30:1052010-01-01 00:30:10
2009-12-14 19:15:2052009-12-19 19:15:20

Add hours

This operation adds an increment value to the hour.

InputIncrementOutput
2011-04-07 23:30:1512011-04-08 00:30:15
2010-03-28 17:07:1212010-03-28 18:07:12

Add milliseconds

This operation adds an increment value to the millisecond.

InputIncrementOutput
2009-04-07 23:30:15100002009-04-07 23:30:25
2010-12-10 17:07:12100002010-12-10 17:07:22

Add minutes

This operation adds an increment value to the minute.

InputIncrementOutput
2009-04-07 23:30:15302009-04-08 00:00:15
2010-12-10 17:07:12302010-12-10 17:37:12

Add months

This operation adds an increment value to the month.

InputIncrementOutput
2009-05-14 09:45:1022009-07-14 09:45:10
2009-12-14 16:20:0022010-02-14 16:20:00

Add seconds

This operation adds an increment value to the second.

InputIncrementOutput
2009-03-28 23:30:15452009-03-28 23:31:00
2011-04-07 17:07:12452011-04-07 17:07:57

Add years

This operation adds an increment value to the year.

InputIncrementOutput
2009-12-14 03:05:1012010-12-14 03:05:10
2011-07-01 19:30:0012012-07-01 19:30:00

Am

This operation transforms the time into Boolean type. If the timestamp value is before noon, it is changed to "true", otherwise it is changed to "false".

InputOutput
2010-12-30 16:51:02false
2011-10-09 11:29:46true

Day

This operation shows only the day from the selected field.

InputOutput
2009-12-14 16:30:0014
2011-06-23 00:20:1023

First of month

This operation changes the day value to the first day of each month.

InputOutput
2010-09-09 10:15:002010-09-01 10:15:00
2008-05-24 10:15:002008-05-01 10:15:00

Hours

This operation shows only the hours from the selected field.

InputOutput
2009-11-25 09:28:079
2011-01-01 17:56:3917

Last of month

This operation changes the day value to the last day of each month.

InputOutput
2010-09-09 10:15:002010-09-30 10:15:00
2008-05-24 10:15:002008-05-31 10:15:00

Max

This operation changes each value in the selected field to a specified maximum value. If the original value is less than the specified maximum, the result becomes the maximum, otherwise it is unchanged.

InputMaximumOutput
2011-04-05 07:30:102011-01-01 12:00:002011-04-05 07:30:10
2010-01-01 16:00:002011-01-01 12:00:002011-01-01 12:00:00

Milliseconds

This operation shows only the milliseconds from the selected field.

InputOutput
2010-09-09 00:30:100
2008-05-31 07:31:410

Min

This operation changes each value in the selected field to a specified minimum value. If the original value is greater than the specified minimum, the result becomes the minimum, otherwise it is unchanged.

InputMaximumOutput
2011-04-05 07:30:102011-01-01 12:00:002011-01-01 12:00:00
2010-01-01 16:00:002011-01-01 12:00:002010-01-01 16:00:00

Minutes

This operation shows only the minutes from the selected field.

InputOutput
2010-05-06 09:28:0728
2009-07-16 17:56:3956

Month

This operation shows only the month from the selected field.

InputOutput
2009-12-14 07:30:1012
2011-06-23 21:01:076

Month name (long)

This operation shows the month value from the selected field in a long name.

InputOutput
2009-12-14 07:30:10December
2011-06-23 00:15:05June

Month name (short)

This operation shows the month value from the selected field in a short name.

InputOutput
2009-12-14 07:30:10Dec
2011-06-23 00:15:05Jun

Null to timestamp

This operation displays the Null timestamp as a specified value, while the other values remain the same.

InputValueOutput
 2000-01-01 00:00:002000-01-01 00:00:00
2009-01-01 07:30:102000-01-01 00:00:002009-01-01 07:30:10

Pm

This operation transforms the timestamp into Boolean type. If the timestamp value is between noon and midnight, it is changed to "true", otherwise changed to "false".

InputOutput
2010-12-31 16:51:02true
2011-09-20 11:29:46false

Quarter

This operation returns an integer to show the quarter to which the specified timestamp value belongs.

InputOutput
2010-11-16 12:30:104
2011-03-28 21:00:051
2011-05-01 17:12:192
2011-08-20 00:30:003

Quarter name (short)

This operation returns a string to show the quarter to which the specified timestamp value belongs.

InputOutput
2010-11-16 12:30:10Q4
2011-03-28 21:00:05Q1
2011-05-01 17:12:19Q2
2011-08-20 00:30:00Q3

Seconds

This operation shows only the seconds from the selected field.

InputOutput
2011-03-28 09:28:077
2009-07-16 17:56:3939

Set day

This operation enables you to set the day value in the selected field.

InputDayOutput
2010-01-01 07:30:1052010-01-05 07:30:10
2008-01-01 21:10:0052008-01-05 21:10:00

Set hour

This operation enables you to set the hour value in the selected field.

InputHourOutput
2011-12-09 00:30:10202011-12-09 20:30:10
2009-03-15 07:31:41202009-03-15 20:31:41

Set millisecond

This operation enables you to set the millisecond value in the selected field. If the specified value is not big enough to calculate into seconds, minutes or hours, the result may look the same with the original value. However, the hidden millisecond value can affect comparing and sorting.

InputValueOutput
2011-12-09 00:30:1012011-12-09 00:30:10
2009-03-15 07:31:4112009-03-15 07:31:41

Set minute

This operation enables you to set the minute value in the selected field.

InputValueOutput
2011-12-09 00:30:1012011-12-09 00:01:10
2009-03-15 07:31:4112009-03-15 07:01:41

Set month

This operation enables you to set the month value in the selected field.

InputMonthOutput
2011-01-01 14:00:0012011-01-01 14:00:00
2009-07-17 23:30:0012009-01-17 23:30:00

Set second

This operation enables you to set the second value in the selected field.

InputValueOutput
2011-12-09 00:30:1012011-12-09 00:30:01
2009-03-15 07:31:4112009-03-15 07:31:01

Set year

This operation enables you to set the year value in the selected field.

InputYearOutput
2011-01-01 12:30:0020112011-01-01 12:30:00
2008-07-17 19:00:0020112011-07-17 19:00:00

Subtract days

This operation subtracts a decrement value from the day.

InputDecrementOutput
2009-12-27 16:30:0052009-12-22 16:30:00
2009-12-05 05:30:0052009-11-30 05:30:00

Subtract hours

This operation subtracts a decrement value from the hour.

InputDecrementOutput
2009-04-26 23:30:1552009-04-26 18:30:15
2011-10-02 17:07:1252011-10-02 12:07:12

Subtract milliseconds

This operation subtracts a decrement value from the milliseconds. If the decrement value is not big enough to affect seconds, minutes or hours, the result may look the same with the original value. However, the hidden millisecond value can affect comparing and sorting.

InputDecrementOutput
2009-04-26 23:30:15302009-04-26 23:30:15
2011-10-02 17:07:12302011-10-02 17:07:12

Subtract minutes

This operation subtracts a decrement value from the minutes.

InputDecrementOutput
2009-04-26 23:30:15302009-04-26 23:00:15
2011-10-02 17:07:12302011-10-02 16:37:12

Subtract months

This operation subtracts a decrement value from the month.

InputDecrementOutput
2009-12-14 22:30:2012009-11-14 22:30:20
2009-01-22 08:15:0712008-12-22 08:15:07

Subtract seconds

This operation subtracts a decrement value from the seconds.

InputDecrementOutput
2011-03-28 23:30:15152011-03-28 23:30:00
2010-12-12 16:00:00152010-12-12 15:59:45

Subtract years

This operation subtracts a decrement value from the year.

InputDecrementOutput
2009-12-27 00:30:1022007-12-27 00:30:10
2011-02-05 16:20:4022009-02-05 16:20:40

Timestamp difference (in days)

This operation shows the timestamp difference in days between the specified value and those from the selected field.

When you use Timestamp difference (in days) on two timestamp values, the time will be ignored. Therefore, "2010-01-02 00:01:00" and "2010-01-01 23:59:00" are only two minutes apart, but the difference in days is 1.

InputValueOutput
2011-01-01 18:00:002011-01-01 12:00:000
2011-01-02 14:00:002011-01-01 12:00:001

Timestamp difference (in ms)

This operation shows the timestamp difference in milliseconds between the specified value and those from the selected field.

InputValueOutput
2011-01-01 18:00:002011-01-01 12:00:0021600000
2011-01-02 14:00:002011-01-01 12:00:0093600000

To milliseconds

This operation calculates the timestamp value into milliseconds since the start of the day.

InputOutput
2010-10-15 12:30:0045000000
2008-05-24 10:15:0036900000

To seconds

This operation calculates the timestamp value into seconds since the start of the day.

InputOutput
2010-10-15 12:30:0045000
2008-05-24 10:15:0036900

Truncate

This operation changes the hour, minute and second values to zero.

InputOutput
2009-12-27 01:10:552009-12-27 00:00:00
2009-01-05 17:59:302009-01-05 00:00:00

Year

This operation shows only the year value from the selected field.

InputOutput
2009-12-14 07:10:302009
2011-06-23 14:32:052011